Query with wild card?

[JAYLO]

Hello,
I have 2 tables, all formatted as text.
Table one has a filed where the leading zero's are truncated. That is 0005117 appears as 5117.
Table 2 contains the leading zero's.
I am trying to write a query that will give me a match when theis occurs.

That is 0005117=5117
Thanks

 

[VicRauch]

Rather than using a join between the two tables, you can use the field from one table as the criteria for the matching field in the other table. To illustrate, Table 1, field ZeroNumber and Table 2, field NoZeroNumber will be the fields that you want to join on. So, with the field from Table 2, NoZeroNumber in the Field row on the query layout, put this in the criteria row of this column:

Str(Val([Table 1].[ZeroNumber]))

This will turn the text number with leading zero into a number (without the zeros), then turn it back into a string (now without the zeros).
HTH,

 

[Andrew Fergus]

Hi

Following on from Vic you could use the CDbl() function to convert the text values to numbers, which also removes the leading zeroes. Can you change the field types to Double or is that not an option?

Andrew