List query not working

[ZOR]

I have a table with a column of 12 digits ECS000000000
I am trying to do a like query to fill a listbox.

It only seems to list records when the numbers are 4 digits from the end, probably due to my having a VAL in the query, but I cannot locate by search ECS111111111

Query in list is:

SELECT DISTINCT Table1.Barcode, Val(Right([Barcode] & '',9)) AS [Tape Number], Table1.ID1
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1
WHERE (((Val(Right([Barcode] & '',9))) Like [forms]![Form1B].[Form].[LNAME].[Caption]))
ORDER BY Table1.Barcode;

Any ideas why its not working. Many Thanks

 

[PHV]

Val returns a NUMERIC value
Like is a STRING comparaison operator
So, I don't understand the purpose of your WHERE clause.

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

 

[ZOR]

Thanks PHV. I know my table data is a string, and VAL is a numeric function. My problem is I am trying to search for a like where the table item could be anything from ECS000000001 TO ECS100000000 or any permutation. I cannot seem to get it together to compare and get a like for any right(9) characters. Outside a query I could do a Right$(Item,9) but how do I do it in a query. Many thanks

 

[PHV]

Outside a query I could do a Right$(Item,9) but how do I do it in a query
Right([Barcode] & '',9) ?

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

 

[ZOR]

Thanks PHV. i tried this in the listbox but I get nothing out (ie in list)

SELECT DISTINCT Table1.Barcode, Right([Barcode] & '',9) AS [Tape Number], Table1.ID1
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID1
WHERE (((Right([Barcode] & '',9)) Like [forms]![Form1b].[Form].[LNAMEB].[Caption]))
ORDER BY Table1.Barcode;

 

[PHV]

What is the value of [forms]![Form1b].[Form].[LNAMEB].[Caption] ?

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

 

[ZOR]

Thanks, I have tried adding * at the end and without. It can be any combination/mulitiple of 9 digits, always starting with 1 or greater.

 

[ZOR]

I think my query has a basic flaw in that I am trying to achieve some sort of pattern match/likeness. If I were to type in a 1, then I would hope the listbox contained ECS000000001, or ECS010000001 or ECS100020001, the same if entered a 2 to give ECS002003001 or ECS200507001 etc. In other words matching the character from the start of the last 9 digit string and followed my any subsequent numbers typed in. How the heck do I achieve that one? Thanks

 

[PHV]

Say you have a TextBox containing a single digit:
WHERE Val(Mid([Barcode],4)) Like [Forms]![yourForm]![yourTextBox] & '*'

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

 

[ZOR]

I tried this, it produces numbers in the listbox, but none that make any sense of being like?

SELECT Table1.ID1, Table1.Barcode
FROM Table1
WHERE Val(Mid([Barcode],4)) Like [Forms]![Form1B]![LNAMEB] & '*'
ORDER BY Table1.Barcode;

I thought when I typed in a 1, it was producing all numbers have a 1 in the number chain, but other items were listed not having a 1 in their numbers. Thanks