Where and statement

[niteraven]

Hello all

I am trying to query one field with a AND statement. It is not working. Here is my sql statement:

SELECT tblData.sonum, tblData.wsnum, tbl.Customer, tblDetails.itemdesc, tblSDetails.itemqty, tblDetails.itemnum
FROM tblData INNER JOIN tblSDetails ON tblData.sonum = tblDetails.sonum
WHERE (((tblDetails.itemnum) Like Nz([forms]![frmLookup]![txtproduct]," ") & "*" And (tblDetails.itemnum) Like Nz([forms]![frmLookup]![TXTHANDHELD]," ") & "*"));

Is it not working because of the like statement?

Any help is greatly appreciated!
Thanks,
raven

 

[lameid]

Unless my eyes are playing tricks on me, it looks like it should run.

 

[niteraven]

It gives no results 100% of the time. I thought it should work too.

 

[lameid]

No results is different than doesn't work...

Your NZ functions are using a space for the null case... I would think you would want a zero length string or "" so that you would get all (*) results when the entry is blank.

 

[PHV]

I'd try this WHERE clause:

WHERE tblDetails.itemnum Like [Forms]![frmLookup]![txtproduct] & "*" AND tblDetails.itemnum Like [Forms]![frmLookup]![TXTHANDHELD] & "*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[niteraven]

lAMEID: You were correct about zero length string.... but it is still not executing the second like statement after and.

PHV: Same thing is happening to your code also.

Thank you for your help thus far. It is appreciated!

 

[Andrzejek]

Do you have your SELECT statement assigned to some sort of variable, like:

strSQL = "SELECT tblData.sonum, tblData.wsnum ..."

Debug.Print strSQL

If so, could you show us what you get from Debug.Print line?

Have fun.

---- Andy

 

[PHV]

Seems suspect to me to use the AND operator on the same column (itemnum) ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Seems suspect to me to use the AND operator on the same column (itemnum) ...

Aggreed.

niteraven do you understand boolean logic?

Do you mean you want things that match this and this.... That is an OR not an AND.... In which case, you should rethink using like.

 

[cheerio]

why is the third field prefixed with tbl which is not a table name or alias?

 

[niteraven]

Thanks guys.

PHV and Lameid: Yes it is suspect, my Boss insists that this should work, i have an OR statement that does work correctly. He wants radio buttons that have AND, OR as choices for this query. I had to try this, as my Boss knows way more than me, and no explaination i was giving was acceptable. EXCEPT this one with two of the best of Tek-tips forums MVPS explaining.

Cheerio: i stripped the beginning of the table name off, must of stripped the Data off with that. It is a name branding thing that i am not allowed to use in here.

thank you all. You are all top notch!
Raven