Access Critereia Query - with blank fields and search parameters

[gautam89]

Hi everyone...

I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.

Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status

Only "Email" is mandatory, ID is autonumber, the rest are optional.

I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"

Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"


The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""


Thanks,
Gautam

 

[rainman89]

Try the NZ function. it will give you what you need. so in the criteria if something has blanks use NZ([fieldname])

 

[ajetrumpet]

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I don't know about the "*" use, I've seen a lot of that though...if nothing else works for you, try the AND's and OR's.

WHERE ([criteria1]=[control1] OR [control1] Is Null) AND (...OR Is Null) AND (etc, etc...)

I've done it with the Nz function too...

WHERE Nz[criteria1]=[control1] AND Nz[citeria2]=[control2] AND (etc...)

 

[petehilljnr]

I've found the best way to do this is create your criteria using VBA.

Create an unbound form with the names of the fields you want to search on as text boxes:

e.g.
[First_Name]
[Last_Name]
[Org]

[Status]

Then, create a search button with code similar to:

[CODE]Sub btnSearch_Click()

Dim strCriteria as String
Dim strSQL as String

strCriteria = ""
strSQL = "Select * from MyTable"

strCriteria = add_criteria(strCriteria, "Last_Name", Me.[Last_Name])
strCriteria = add_criteria(strCriteria, "First_Name", Me.[First_Name])
strCriteria = add_criteria(strCriteria, "Org", Me.[Org])
strCriteria = add_criteria(strCriteria, "Email", Me.[Email])
strCriteria = add_criteria(strCriteria, "Status", Me.[Status])

If len(strCriteria) <> 0 Then
strCriteria = " WHERE " & strCriteria
End if

strSQL = strSQL & strCriteria

'*** PART B

End Sub

Function add_criteria(byval strCurrentCriteria as String, byVal strField as String, byVal strNewValue as Variant) as String
'note that this function only deals with string values being searched, not numeric or date data types.

If isnull(strNewValue) then
add_criteria = strCurrentCriteria
Exit Function
End if

if len(strCurrentCriteria) <> 0 Then
add_criteria = " AND [" & strField & "] Like " & Chr(34) & "*" & strNewValue & "*" & Chr(34)
Else
add_criteria = "[" & strField & "] Like " & Chr(34) & "*" & strNewValue & "*" & Chr(34)
End If

Exit Function[/CODE]

At the moment, you've got strSQL which is your query string.
You can do anything you want with that now:
e.g.:
[B]Open a form with the results[/B]
Create a DataSheet form based the table or query with the data and in PART B of the code above write: DoCmd.OpenForm "MyForm", acFormDS, , strCriteria

You can create a query based on that. You can edit an existing query based on that (or even better, create a form based on QueryA, then edit QueryA with strSQL using DAO then open that form).

Regards,
Pete.

 

[Banana]

This is remarkably ineffective. Not only you're pulling all records over the wire, you're doing a lot of work to re-invent the wheel.

But whatever works.

 

[petehilljnr]

This is remarkably ineffective. Not only you're pulling all records over the wire, you're doing a lot of work to re-invent the wheel.

But whatever works.

How are you "pulling records all over the wire" when you're only defining the query parameters?

Correct me if I'm wrong but you're only "pulling records across the wire" when the query is executed - in which case how would this be different from running a "normal" query?

Agreed there are probably simpler approaches.

 

[Banana]

Based on my understanding of the engine, because we're doing this in VBA, there is no execution plan, which make the query run more effective & faster. I had a notion that whenever JET (the engine that does SQL processing) has no execution plan, it will take all records from the back-end and process it locally, criteria or no criteria.

Only if you use a stored query, does it work better. (You don't have to store queries you build using query builder in form design view as it's also stored as well. Only when you're doing this within VBA does this cause problems)

Furthermore, if you use "SELECT * FROM MyTable", you have the benefit of being able to dynamically update field list but at a penalty of extra overhead JET has to execute in resolving what fields there are. As fields usually requires a control to be bound to, it makes little sense to use *, and we can get better result by enumerating what fields we want to pull.

Here's what I'd do- Create a normal query that gives me what fields I need for the form. If you right-click on the blank space where you have tables in query builder, you can select "Parameters" which you then get a dialog box. You would type in criteria there and specify data type. When you have all parameters you need, add parameters to the criteria row for appropriate columns. Be sure to remember to enclose the parameter in brackets.

Now, if you run the query, you'll get a little input box asking you for parameters; you can then enter criteria and it'll execute with the criteria generated by parameter.

But if you don't want to see that box, you can do that in VBA.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQueryName")

qdf!ParameterName = Value

Set rst = qdf.OpenRecordset

Furthermore, if you will have the value for criteria on the form, you can actually bind the query to the control without having to use a parameter at all.

Right-click on the criteria cell and select "Build..." which you will get Expression Builder. Make this your bestest friend EVAR. From there, you want to open "Forms -> Loaded Forms -> MyFormName" then in adjacent listbox, select the approrpriate control. It's then inserted into the query.

I hope that helps.

 

[Jon K]

Orignially posted by gautam89
Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"

The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

You can put the criteria for each field in a new column in the query grid like this:-
--------------------------------
Field: [FirstName] Like "*" & [forms]![Search]![txt_FirstName] & "*" Or [forms]![Search]![txt_FirstName] Is Null

Show: uncheck

Criteria: True
--------------------------------

See this thread and its notes for explanation.
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
.