Alike query with blank fields (1 Viewer)

[tahoe1322]

Hello,

I am very new to access and am self taught so thank you for your patients.

I have developed a search box in a preexisting database. I am using an "alike" query to make the searching easier. The issue is that some of the fields are not required (so they are often left blank) but I still want them to be searchable. When I put the alike query for these non required fields, only data that has values is returned, even if the search box for that field is blank.

I am using:
ALike "%" & [Forms]![frmSearch]![frmSearch1].[Form]![txtLastName] & "%"

For example, the first name is not required but the last name is. There are several records where the first name is left blank. So if I put the alike statement in the first and last name Criteria section in the query, and I search for the last name "Johnson" only record 2 will be a result. If I put the alike statement only for the last name, both record 1 and 2 will be the result. But the problem is I cannot search by first name.

Example table:
Record First Name Last Name
1 Johnson
2 Robert Johnson


Any help would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

Try using something like this:

 ALike "%" & [Forms]![frmSearch]![frmSearch1].[Form]![txtLastName] & "%" OR Is Null

Hope that helps...

 

[arnelgp]

maybe you have used AND (criteria on same line) on your query instead of OR (criteria on after the other).

 

[The_Doc_Man]

Slight modification to what theDBguy suggested because you said the FIRST name was optional but the last name was not.

SELECT some fields FROM some table
WHERE ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtLastName & "%" AND
     ( ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtFirstName & "%" )
     OR ( Nz(Forms("frmSearch")!frmSearch1.Form!txtFistName),"") = "" ) ;

I included extra parentheses for clarity.

 

[Pat Hartman]

ALIKE ??? is that some new SQL keyword?

 

[theDBguy]

ALIKE ??? is that some new SQL keyword?

Not new, it's old. It's part of ANSI 92 SQL standard.

 

[sxschech]

How to use the SQL ALike operator

Explains how to use the SQL ALike operator and also sql like, includes examples

www.codevba.com

If I read this correctly, is it just allowing you to use % _ interchangeably for * ? ?

 

[theDBguy]

How to use the SQL ALike operator

Explains how to use the SQL ALike operator and also sql like, includes examples

www.codevba.com

If I read this correctly, is it just allowing you to use % _ interchangeably for * ? ?

No, that's not the purpose of it. It's a requirement. If you use ALike, then you have to use % instead of *. It doesn't "allow" you to use % - you have to. It's the "standard" (ANSI 92).

As a comparison, when you use date values in Access, you'll have to delimit them using # tags. But if you use date values in SQL Server, you have to use ' tags (single quote). So, they're just different ways to do the same thing on two separate systems. You can't use ALike in Access unless you have set it up to be ANSI 92 compliant (you should get a syntax error).

 

[Isaac]

No, that's not the purpose of it. It's a requirement. If you use ALike, then you have to use % instead of *. It doesn't "allow" you to use % - you have to. It's the "standard" (ANSI 92).

As a comparison, when you use date values in Access, you'll have to delimit them using # tags. But if you use date values in SQL Server, you have to use ' tags (single quote). So, they're just different ways to do the same thing on two separate systems. You can't use ALike in Access unless you have set it up to be ANSI 92 compliant (you should get a syntax error).

I am able to use Alike without having the Ansi 92 standards turned on.

To make it more complicated, depending on your environment/settings, you may type Alike and it gets auto changed to Like...But the "Alike-friendly" syntactical symbols you've entered don't get changed to the Like-friendly ones.

 

[tahoe1322]

Slight modification to what theDBguy suggested because you said the FIRST name was optional but the last name was not.

SELECT some fields FROM some table
WHERE ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtLastName & "%" AND
     ( ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtFirstName & "%" )
     OR ( Nz(Forms("frmSearch")!frmSearch1.Form!txtFistName),"") = "" ) ;

I included extra parentheses for clarity.

Thanks everyone for the help!

I am using the alike statement in Criteria area of a query. Is there a different version of the above for that area? I am not very experenced in Access....

 

[isladogs]

If you aren't using SQL Server, make sure the use sql server compatible syntax option is unticked in Access options

Now change your query as follows: Replace ALIKE with LIKE and replace % with *

 

[tahoe1322]

Thanks everyone for the help!

I am using the alike statement in Criteria area of a query. Is there a different version of the above for that area? I am not very experenced in Access....

Slight modification to what theDBguy suggested because you said the FIRST name was optional but the last name was not.

SELECT some fields FROM some table
WHERE ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtLastName & "%" AND
     ( ALIKE "%" & Forms("frmSearch")!frmSearch1.Form!txtFirstName & "%" )
     OR ( Nz(Forms("frmSearch")!frmSearch1.Form!txtFistName),"") = "" ) ;

I included extra parentheses for clarity.

So I am trying to look back into this.

I think this is close but not exactly what I am after. Here are some more details:

• On the search box all search fields are optional.
• On the table where the entries are, the last name is mandatory but the first name is not.
• 
  
• So with the current query, if the entry on the table does not have a first name, it gets passed completely even if the last name matches and there is no first name in the search field.

Also there are many different search fields other than first and last name, so I am looking for a code to put in the "criteria" field of the query if possible.

Thank you everyone for your help!!

 

[theDBguy]

Hi. There are plenty of demo on this topic you can study. Try doing a search for "search forms."

I tend to build the criteria, one condition at a time, and then pass it to whatever object I'm trying to apply it.