Criteria expression in Design Mode not getting what's desired

[GraeagleBill]

I'm designing a query in design mode from which I capture the SQL to be used to create a DAO recordset in code. I want Jet to do as much of the work as possible, so I created the query this way:




I've already gathered the ID1, ID2,,,,,,,ID6 into a single string and the idea is to refresh the DAO recordset with each number to be searched. I was hoping the InStr function would then limit the records to those where the number was found. As coded I get this:


What I want in this example is for SQL to return ONLY the two records you see where I've highlighted with the arrows, namely the two strings that contain the value 180.

Is there a syntax error in the InStr function?

[CJ_London]

if groupings is a table it looks like your data is not normalised so results in a complex and possibly unworkable query
in sql parlance, # relates to date types so should not be used in this way
instr returns a number, so using len (a string function) won't work

It is not clear what you are trying to achieve so can only speculate what a solution may be. Suggest explain in more detail what 'the idea is to refresh the DAO recordset with each number to be searched. I was hoping the InStr function would then limit the records to those where the number was found.' really means - use examples and explain your table relationships

[JoeM]

A few things:

1. If a value is not found in the InStr function, it returns a 0. If you apply the Len function to 0, it returns a 1. So, if you are applying that sort of criteria, you do not actually want/need the Len function. Also, you do not want double-quotes around your field name [strID]. If you do, it is checking the literal text string [strID] and not the calculated field [strID]. So, the criteria would look something like (however, read my next part).

=InStr([strID],"#180#")>0

2. I have had trouble in the past with having a calculated field in a query, and then try to apply criteria to that calculated field in the same query when trying to access it by the calculated field name, instead of the underlying calculation. So you might need it to look something like this:

=InStr("#" & [ID1] & "#" & [ID2] & "#" & [ID3] & "#" & [ID4] & "#" & [ID5] & "#" & [ID6] & "#","#180#")>0

3. If it were me, I would not use either of those two criteria. I would just use this instead:

WHERE [ID1]="180" Or [ID2]="180" Or [ID3]="180" Or [ID4]="180" Or [ID5]="180" Or [ID6]="180"

[CJ_London]

on the basis the IDs are numbers you would use

WHERE [ID1]=180 Or [ID2]=180 Or [ID3]=180 Or [ID4]=180 Or [ID5]=180 Or [ID6]=180

[JoeM]

on the basis the IDs are numbers you would use

Good point. I shouldn't have assumed that those values were String/Text.

[GraeagleBill]

Both

WHERE [ID1]="180" Or [ID2]="180" Or [ID3]="180" Or [ID4]="180" Or [ID5]="180" Or [ID6]="180"

and

WHERE [ID1]=180 Or [ID2]=180 Or [ID3]=180 Or [ID4]=180 Or [ID5]=180 Or [ID6]=180

Work as desired. The resulting SQL form of the query becomes a bit unwieldy in appearance. "180" was just my test value in trying to get the query to work testing for the existence of a particular value among the IDx fields in the GROUPINGS table. In the implementation of the app, there will be a VBA integer variable that will be what is being search.

So, if my search value is say intSrch , then the WHERE clause looks like this:

WHERE (([ViewWith] = True) AND ("[ID1]=" & intSrch & Or "[ID2]=" & intSrch & Or "[ID3]=" & intSrch & Or "[ID4]=" & intSrch & Or "[ID5]=" & intSrch & Or "[ID6]=" & intSrch))

I'll post the Function code that uses the query with a DAO recordset later today.

Thanks,
Bill

[GraeagleBill]

The SQL SELECT string in code became a syntax nightmare. I had to move on with more pressing issues, so I relented and did the "strID" string search in code.

Thanks for the thoughts given to this post.
Bill