How to make this sql RecordSource string

[zx3]

I have a table called inventory as following
id code qty
1 -A-B- 10
2 -G-H- 11
3 -O-G- 4

id is auto generated, code is text, qty is integer
i use a form to list the record from the table, the only criterion is the code of the record must be Instr("-O-G-H-" ,[code])>0 , ie the code of the record must be a substring of "-O-G-H-" , how do i make the sql string?

I tried this (just the crucial lines)


nostr = "-O-G-H-"
Me.RecordSource = "SELECT * FROM inventory WHERE ((InStr('" & nostr & "', code)) > 0);"

It pull some record out but the records are wrong, the code is not substring of the nostr.

Any idea?

[pbaldy]

I believe you have the arguments of the InStr() function reversed.

[zx3]

I believe you have the arguments of the InStr() function reversed.

Thanks for the reply, and the arguments of the InStr function is not reversed in StInr('" & nostr & "', code) , code is the search string and nostr is the string, i want to see if code is part of nostr.

If it is StInr(code '" & nostr & "'), then no record will return as nostr is always a longer string than string from the field code.

[pbaldy]

Correct me if I'm wrong, but "code" is the field to be searched and "nostr"contains the value you're searching for.

[zx3]

Correct me if I'm wrong, but "code" is the field to be searched and "nostr"contains the value you're searching for.

Yes, you're correct.

I search through the web, when people use InStr, in most case, they try to see if a string can be found in a field, but my case is to see if the field can be found in a specific string (nostr), if yes, list it on the form.

[pbaldy]

I think I understand. Can you post the db to play with?

[zx3]

Here's the database Attachment 4296, the record actually need to be pulled from 3 tables, I use the sql string to put them together before trimming it down with the InStr criterion.

[pbaldy]

The UNION is the problem. I think you need the WHERE clause on all 3 parts:

Me.RecordSource = "SELECT * FROM Location1 WHERE ((InStr('" & nostr & "', code)) > 0) UNION SELECT * FROM Location2 WHERE ((InStr('" & nostr & "', code)) > 0) UNION SELECT * FROM Location3 WHERE ((InStr('" & nostr & "', code)) > 0);"

That appears to only pull records that match the test string.

[zx3]

The UNION is the problem. I think you need the WHERE clause on all 3 parts:

Me.RecordSource = "SELECT * FROM Location1 WHERE ((InStr('" & nostr & "', code)) > 0) UNION SELECT * FROM Location2 WHERE ((InStr('" & nostr & "', code)) > 0) UNION SELECT * FROM Location3 WHERE ((InStr('" & nostr & "', code)) > 0);"

That appears to only pull records that match the test string.

Thanks Paul, it works very well, thanks for your time.

[pbaldy]

No problem; sorry I misunderstood the goal at first.