Problems with Excel Import and Inconsistent Commas

[tig]

I've got an Access application that I import Excel files into.
Sometimes a number will import into my table with commas separating
thousands, sometimes not (e.g. sometimes 2125.01, sometimes 2,125.01).
When I look at my excel source, the cell formats are the same and the
value in the "=" input box both would show 2125.01 (no comma).

Where this leads to a problem in Access is on my join between two
tables. The main table shows 2,125.01 the imported table shows
2125.01. So the join fails to see it as a match.

So, either I need to resolve the inconsistent import issue or find a
way to make the join see the two numbers as a match.

Any suggestions, greatly appreciated. This is driving me nuts.

TIA

 

[Douglas J. Steele]

If Access is treating 2,125.01 as different than 2125.01, there are two
possibilities.

One is that you've got round-off error (perhaps the first one is really
2,125.0100001 and the second one is really 2125.010003), and that other is
that you're talking about text fields.

If your fields are Text fields, why are they text fields and not an
appropriate Numeric type?

If they are numeric, and the problem is round-off, you may have to go into
the SQL of your queries and change

Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1

to something like:

Table1 INNER JOIN Table2 ON Abs(Table1.Field1 - Table2.Field1) < 0.001

or whatever tolerance you want.

 

[tig]

Thanks Doug that worked great. The other thing I just found to work
was to append the records to another table using the val() function.
That seemed to convert the numbers properly

Thanks again.