Blank criteria

[mcginty]

Hi All,

I have a subform (based on a query) with criteria pointing at two (and possibly more in the future) drop down boxes.

This works fine when all criteria is selected but if one drop down box is not chosen, no results are displayed.

How can I prevent this?

If only one dropdown is chosen I want all records for that single criteria.

Thanks in advance
Ted

 

[randysmid]

Hi,
You can check for any value with code, and if nothing has been selected, you can send a "%" to the query. The percent sign is a wildcard character in Access. If you are using some other database, such as Oracle, you will need to find out what the wildcard character is for that particular database.
HTH,
Randy Smith
California Teachers Association

 

[chrissie1]

i think in mdb' s the wildcard is *
and in adps (sql-server- its %

"What a wonderfull world" - Louis armstrong

 

[BillPower]

For your current scenario:

Create 2 text boxes on your sub form, txtCombo1, txtCombo2. Visible = No > Default Value = "*"

In the After Update of both Combo’s

If Not IsNull(Me!MyCombo1) Then
Me!txtCombo1 = "*"
Else
Me!txtCombo1 = Me!MyCombo1
End If

If Not IsNull(Me!MyCombo2) Then
Me!txtCombo2 = "*"
Else
Me!txtCombo2 = Me!MyCombo2
End If

Your query or recordset would like this:

SELECT *
FROM YourTableName
WHERE (((YourField1) Like [Forms]![YourFormName]![txtCmb1]) AND ((YourField2) Like [Forms]![YourFormName]![txtCmb2]));color]

 

[BillPower]

Oops…..
For your current scenario:

Create 2 text boxes on your Main Form, txtCombo1, txtCombo1. Visible -> No > Default Value -> "*"

In the After Update of both Combo’s

If Not IsNull(Me!MyCombo1) Then
Forms!YourFormName!txtCombo1 = "*"
Else
Forms!YourFormName!txtCombo1 = Me!MyCombo1
End If

If Not IsNull(Me!MyCombo2) Then
Forms!YourFormName!txtCombo2 = "*"
Else
Forms!YourFormName!txtCombo2 = Me!MyCombo2
End If

Your query or recordset bound to the sub form would like this:

SELECT *
FROM YourTableName
WHERE (((YourField1) Like [Forms]![YourFormName]![txtCmb1]) AND ((YourField2) Like [Forms]![YourFormName]![txtCmb2]));

This should do what you want.