Multiple Variable Criteria

[SysDupe123]

I've got a query that I'm trying to make using anywhere from 1 to 3 criteria that are drawn from a form. The form has 3 list boxes to select various criteria: date, client no., and investor group. The user would select any combination of the three and I'm trying to get the query to output the result. I can't seem to set up the query so it will work. I can't use more than one criteria without it coming back with no records.

If I put all 3 criteria in, then I get records back.
I tried to indicate if the form field is null, then Like"*".

I've been working on this for awhile now.

 

[AlexMidd]

Do you have an example of what you have done so far?

Have fun! )

Alex Middleton

 

[rac2]

Use the first option in a list box to indicate that any value is acceptable, in other words that field should not affect the rows selected. The first option might have a value such as "na", "", " ", or zero, whatever.

Use a logical OR in the WHERE clause for each field. In words, if the form field equals option 1 OR the form field equals the value in the row of the table. Combine the three fields with logical AND.

For example-

...
WHERE
       ( [client_no_from_form] = "" OR ClientNo = [client_no_from_form] )
  AND
       ( [date_from_form] = "" OR DateOfInvestment = [date_from_form] )
  AND
       ( [group_from_form] = "" OR InvestorGroup = [group_from_form] )

 

[Wolfen02]

I've had success using the default value of "*" for controls. This way you're not dependant on the user selecting a value in each field.

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[SysDupe123]

Thanks everyone!
I ended up using multiple If statements, building the SQL statement with each one, then inserting it after the WHERE statement.

example:
If [Forms]![Reports]![date] <> "" Then
SQLCrit = SQLCrit & "[MasterDate] = " & Chr(35) & _
[Forms]![Reports]![date] & Chr(35)
End If

If SQLCrit <> "" And [Forms]![Reports]![Group] <> "" Then
SQLCrit = SQLCrit & " and "
End If

If [Forms]![Reports]![Group] <> "" Then
SQLCrit = SQLCrit & "[GroupCode] = " & Chr(34) & _
[Forms]![Reports]![Group] & Chr(34)
End If

"....WHERE (" & SQLCrit

Works great.