Like and extra 000's

[RogueIT]

In tableA.upc I have a field that holds 697807070369 for a value
in tableB.upc I have a field that holds the information but has leading zeros.
Also not all items have the same amount of leading zeros.
How can I query those fields to show me the information for tableB.upc?
thanks,
Scott

 

[Rick B]

If it were me, I'd fix my tables. This is either a number or it is text.
It sounds like you have it as one in one table and the other in the other
table. If the leading zeros are important, change them to text and make the
entries consistent. Items like serial number, barcodes, UPCs, should NOT be
stored as numbers. Typically number fields are used only if you need to use
typical number formats or mathematical calculations. Serial numbers, UPCs,
barcodes, generally should be stored in a text field.

 

[Marshall Barton]

In tableA.upc I have a field that holds 697807070369 for a value
in tableB.upc I have a field that holds the information but has leading zeros.
Also not all items have the same amount of leading zeros.
How can I query those fields to show me the information for tableB.upc?

Assuming those are all TEXT fields, this might so what you
want:
tableB.upc Like "*" & tableA.upc

but if tableA contains a code like 56789 and tableB has one
like 123456789, it will still match.

If that kind of combination could occur, then you will need
to write your own compare function.