Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > Format table cells : 123,456,789.00 <=> 123.456.789,00

Thread Tools Display Modes
Old 03-17-2004, 04:41 AM
Apu Apu is offline
Join Date: Mar 2004
Location: Munich
Posts: 36
Unhappy Format table cells : 123,456,789.00 <=> 123.456.789,00

Hi, I've got two tables in my Access which are fed with data from two corresponding excel sheets.

Since I work in Germany, we often get data in German numeric format ("," to separate thousands and "." to separate decimals) but we also get data in American format ("." to separate thousands and "," to separate decimals).

123,456,789.00 <=> 123.456.789,00

Now I want to compare those two tables and see if all cells are equal. Of course, if I have one table with German format and one with American, the cells will never be equal.

What do I do? I want it all hard-coded in Access with the user having to determine which table has what format and according to his choice (radio buttons) I will transform one table to the opposite format and then start comparing.

All cells are full only of numbers but they are all formatted as Strings.

Can anybody help? It iss very important to me.
Reply With Quote
Old 03-18-2004, 09:25 AM
Timbo's Avatar
Timbo Timbo is offline

Super Moderator
* Guru *
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261

Originally Posted by Apu
All cells are full only of numbers but they are all formatted as Strings.
This is the root of your troubles! Had the values been stored as numbers, Access could display them in whatever custom format you desire.

If you are certain that the tables fields are defined as 'Text' data types, you will have to undo this process.

You could create some new tables with the required fields changed to a Double data type, then use an append query to read the original text from the source tables, and use some string manipulation functions to remove the 1000 mark commas/periods from the values before appending them to the correctly formatted table.
The display format of the corresponding form could then be toggled by the user using radio bottons as you describe.
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
"save-as" function automatic responses delljohnb Excel 7 11-18-2003 01:14 PM
Def. of Text Format in Active Cells dopn2187 Excel 2 10-16-2003 12:55 AM
Incorrect date format stored in table RattleSnake Database and Reporting 5 02-07-2002 01:16 AM
Multiple Table Query? Rezner Database and Reporting 0 01-08-2002 03:55 PM
Update Table with SQL kokZai Database and Reporting 11 10-25-2001 08:24 PM


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..