Query Text file to DBF

[Frank]

I'm having a problem running a query using a text file (pulled from an
AS400) and a DBF file (pulled from an external application). The
fields I want to match are account numbers. In the DBF file, it's a
text field, 25 characters, and the input is all left-justified. In the
text file, the field is 16-characters, and the numbers are all right
justified. All the account numbers contain nothing but digits.

I've tried comparing the Trim() of the numbers and get no matches.
I've tried a query creating an expression using the Val() of the
numbers then querying on that, and I get a data type mismatch. I'm
comparing numbers to numbers, but what the hey; I'm obviously a novice.
Any suggestions would be appreciated.

 

[John Spencer (MVP)]

PERHAPS,

Trim(DBF.Account) = CStr(As400.Account)

Otherwise try checking for "hidden" characters in one or both of the fields.

You could also try, something like the following.
Left(DBF.Account,16) = Right(AS400.Account,16)

 

[Frank]

Thanks for the response, John. I finally imported the data into an
Access table rather than linking. Once done, I trimmed both account
number fields, and the comparison worked fine.

I wanted to link the tables so that I could just replace them as
necessary and have the queries still work.

I'm still mystified why the val() comparison wouldn't work. Wouldn't
val(" 1234") = val("1234 ")?

 

[John Spencer (MVP)]

Well, it does, but that assumes that those spaces are space characters and not
something else.

 

[Frank]

Ah. I'd forgotten that what walks and talks like a space is not always
the character made with the spacebar ;o) Thanks for the help.