Building SQL String as Sub for no Value

[scarlo]

I'm running a 9 parameter query, where values for the criteria are drawn from a form. I'm confused as to how I build an "if, then" string so that if one of the parameters is left blank, the program will skip it and go to the next.

Any suggestions?

 

[WayneRyan]

scarlo,

Basically just traverse your search textboxes building a Where clause.

strWhere = ""

If Not IsNull(Me.txtSearch1) Then
   strWhere = "Where Field1 = '" & txtSearch1 & "'"
End If

If Not IsNull(Me.txtSearch2) Then
   If Len(strWhere) = 0 Then
      strWhere = "Where Field2 = '" & txtSearch2 & "'"
   Else
      strWhere = " And Field2 = '" & txtSearch2 & "'"
   End If
End If

And so on ...

Note: If you name them like "txtSearch1, txtSearch2 ...
Then you can use a loop to traverse them using Me.Controls("txtSearch" & CStr(i))

Note: You can make a query and have no code:

Select *
From   YourTable
Where (Field1 = Forms![YourForm]![txtSearch1] Or
       Forms![YourForm]![txtSearch1] Is Null) And
      (Field2 = Forms![YourForm]![txtSearch2] Or
       Forms![YourForm]![txtSearch2] Is Null) ... and so on

Wayne

 

[scarlo]

Hey Wayne, that looks good. A small issue:

I put in "Where (Field2 = Forms![Sort]![Contact] Or Forms![Sort]![Contact]! Is Null) as criteria in my second query field and I'm being toldthat a parenthesis or dot(.) is invalid..

Tried a few things otherwise and did not have any luck. Any advice on this one?

 

[Mile-O]

I put in "Where (Field2 = Forms![Sort]![Contact] Or Forms![Sort]![Contact]! Is Null)

Look at the red mark

 

[scarlo]

Ok, thanks a lot!

 

[scarlo]

New Question

Ok, what I got going in my query design.

In my criteria for one of the fields in the query there is:
Criteria: [Forms]![Sort]![Contact]
Or: [Forms]![Sort]![Contact] Is Null

This brings back information only if I put in a value on the form, when I don't put in a value the

return is totally blank.

When I recode to:
Criteria: Where Contact Like Forms![Sort]!Contact
Or: Forms![Sort]![Contact] like "*"

I get both the ones I need and all the rest (because of wildcard). How do I get the search to return

ONLY the value I put on the form, OR everything if no value is specified. Basically I want something

like:

Criteria: [Forms]![Sort]![Criteria]
Or: Where Contact Like Forms![Sort]![Criteria] = null, sub "*"

Any advice?

 

[Mile-O]

Put [Forms]![Sort]![Contact] OR [Forms]![Sort]![Contact] Is Null as the criteria (don't split it over two lines) and save the query. When you go back in the change will be made.

 

[scarlo]

Ok, but that returns only when I have a value in the form area associated with that parameter.

What I really want is if there is a value on the form for a particular parameter for it to be used, but if there is not one that it skips that parameter using "*".

Something like :
[Forms]![Sort]![Contact]
OR if Is Null, Like "*"

Anymore ideas?

 

[scarlo]

http://www.mvps.org/access/queries/qry0001.htm

Even that says the same, but when I put in
[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

but this does not seem to work at all.. I get the right response if I put in a value in the dropdowns or nothing if there is none or either missing. =/.