Query criteria problem 2

[ZOR]

I did think this problem was behind me, but its bounced back. I am trying to build a user selection list of records against an input box.
Records in the table are similar to:

01000000
00100678
00012300
00611100
etc

The numbers are text. I am trying to make it so if a 1 was entered in the search box, records 01000000,00100678,00012300 would appear in the listbox. If a 2 followed the 1, ie 12, then only record 00012300 would appear, furthermore if just a 6 were entered then only record 00611100 would appear.

I have tried using Val, etc, but am not getting anywhere.

This code only shows a record when its totally matched.

SELECT TXMASTERS.ID1, Val(Right([Barcode],9)) AS [AT]
FROM TXMASTERS
WHERE (((Val(Right([Barcode],9))) Like Val([FORMS]![MainForm1].[LNAME].[CAPTION])));

I am putting the textbox value into a label for ease of seeing whats going on, as 3 alphanumeric letters get stripped from the record numbers searched for. Many thanks

 

[PHV]

Perhaps this ?
WHERE Val(Right([Barcode],9)) & '' Like Val([FORMS]![MainForm1].[LNAME].[CAPTION]) & '*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

How about:

SELECT TXMASTERS.ID1, Val(Right([Barcode],9)) AS [AT]
FROM TXMASTERS
WHERE Right([Barcode],9) Like "*" & Trim([FORMS]![MainForm1].[LNAME].[CAPTION]) & "*";

 

[ZOR]

Many thanks both of you, worked like a dream. So pleased to see the back of that, have a star each. Best regards