"Like" criteria statement - do not get ALL 1

[knuckelhead]

I have a Qry that does not return ALL the records when I hit Enter. If I do not use the LIKE Criteria statement, my ALL results are perfect. So it is my LIKE statement.

I have tried the "LIKE" below with no success:

Like [Enter Last 2 FormulaID digits OR Enter for ALL:] & "*"


Like "*" & [Enter Last 2 FormulaID digits or Enter for ALL] & "*"


Any ideas?
thanks

 

[PHV]

Like "*" & [Enter Last 2 FormulaID digits or Enter for ALL] & "*" OR [Enter Last 2 FormulaID digits or Enter for ALL] & "" = ""

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

 

[knuckelhead]

Excellent. I spent 5 hours today trying to figure this out and you did it in 30 seconds.

I will donate again to the club for this and a different pending question that i have not gotten to yet. Thanks for your expert simple answers.

 

[PHV]

pending question that i have not gotten to yet
Which ?

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

 

[knuckelhead]

i meant that i haven't studied the response yet to my other question about Sales detail records. I hope i get a chance to read the sites answer tomorrow. i see that 2 people responded. this is a terrific site. thanks for asking anyway. You always follow up.
Knucklehead

 

[jjlogan]

PHV - I have not seen the second construct(ion) of the above query ... OR [Enter ...] & ""="".
I tried it against an Access table and got no records returned. But the first construct works just fine for me and I have used the 1st many times.
I would like to learn more about the 2nd part of the criteria following the "OR" operator. Specifically, I've never seen a query criteria where the "=" sign follows the parameter.
Thanks for any input.
Jeff

 

[Golom]

In the second part of the query PHV is testing if your parameter is equal to a zero-length character string

[Parameter] & "" = ""

The WHERE clause is satisfied (i.e. it evaluates to TRUE)

If the field is LIKE the Parameter

OR

the parameter is an empty string.

The construct [Parameter] & "" = "" just concatenates an empty string to the parameter in case the parameter is NULL. The result of that is that

NULL & ""

produces an empty string and the test works. Without that the test

[Parameter] = ""

will return FALSE if [Parameter] is NULL because NULL is not equal to anything (including itself!)

 

[jjlogan]

Golom,
Thanks a lot for the help. Now have two questions:
1) Why doesn't the first part of PVS's criteria handle the empty parameter situation in that Like "*" & [parameter] & "*" allows for empty parameter and returns all?
2) The 2nd part of criteria seems to have " signs in wrong place. Shouldn't it be: OR [Enter Last 2 FormulaID digits or Enter for ALL] & " = """ ? versus & ""=""?
3) When I use parameter in my criteria related to a text field, I only use the first part. Is the 2nd part of criteria used when you don't know if field is text (is that the purpose of 2nd part)? Or, is 2nd part necessary for some other reason?
Jeff - Sorry for rambling a bit.

 

[PHV]

in that Like "*" & [parameter] & "*" allows for empty parameter and returns all?
don't returns Null values of the tested field

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

 

[jjlogan]

Thanks Golom and PVS. I now see what the purpose - getting Null valed records in the recordset.
Jeff

 

[knuckelhead]

Hello. I found a glitch in the above LIKE.
The statement has trouble handling codes 10 and 100. When i type in code 10, i am also getting the answers for code 100 in the qry result.

Just to be sure, i recoded my records using code 100 into code 111.

Then i ran the LIKE qry again, asking just for code 10 like before, and sucessfully got just code 10 records.
So somehow the above LIKE is taking my response of 10 and thinking I asked for 10* which would get 100 too.

How can i get the LIKE statement to just get what I ask for eg only code 10?
thanks alot

 

[PHV]

Like "*" & [Enter Last 2 FormulaID digits or Enter for ALL] [!]& "*"[/!] OR [Enter Last 2 FormulaID digits or Enter for ALL] & "" = ""

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

 

[knuckelhead]

Perfect. I recoded my 111 records back to 100 and ran the change. All works well. Thanks alot.