String and numeric data comparison

[mschwent]

I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).

I do not have the ability to change the way I receive the data, they are on linked tables of different customers.

I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.

How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.

 

[KeithG]

First make a query from the table with the text data type. Make a column similar to this:

ConvertText: Mid([text],2,5)

[Text] being the field name

Now save and close that query and create a new query with the table with the number data type. Make a column similar to this one:

NumberConvert: IIf(Len([number])=4,"0" & Trim(Str([number])),Trim(Str([number])))

[Number] being the name of your field

Now save this query and make a another new query from the two queries you just created with a join on CovertNumber= ConvertText

Now anytime you need to see which table contains both values you just have to open up the third query you made. You could type the SQL statement write into VBA if you wanted too. Do you have to view the results or do any data munipulation with the query?

 

[mschwent]

Thank you much, that worked just fine.

Based on the results I manipulate other data, but this data does not need to be touched again, it is all input from a bar code scanner.

The only problem was that the scanner can't distinguish between the bar code of the order number that tells which products are included, and the bar codes of the products themselves, but that problem has just been solved. Thanks again!

Mike