Here is an idea which uses some VBA code. I think you want to test for records with e.g. HIGH in Field 1. If there are no records matching this, you want to search Field 1 for records cotnaining a different value e.g. SMH.
To make this work, you need to run the query from e.g. a command button on a form, so you can run the VBA to set the parameters first.
Create the command button using the wizard, and set it to run your query.
In a VBA module, write this code:
Code:
Public strParameter1 as string
Public Function GetParameter1()
'--------------------------------------------------------
'- Function to get Parameter1 from the global -
'- variable, in places such as queries where you cannot -
'- use the global variable directly -
'--------------------------------------------------------
GetParameter1 = strParameter1
End Function
Add some lines to the code which Access created in the On_Click event of your command button:
Code:
Private Sub btnRunQuery_Click()
On Error GoTo Err_btnRunQuery_Click
Dim stDocName As String
Dim intCount As Integer
stDocName = "qryYourQueryName"
'--------------------------------------------------
'- This code sets the default values for the two -
'- query parameters -
'--------------------------------------------------
strParameter1 = "HIGH"
'--------------------------------------------------
'- This counts the number of records which match -
'- the first parameter -
'--------------------------------------------------
intCount = DCount("[RecordID]", "tblYourTableName", "[Field1]='" & strParameter1 & "'")
'--------------------------------------------------
'- If no records match the first parameter, -
'- change to the second parameter -
'--------------------------------------------------
If intCount = 0 Then
strParameter1 = "SMH"
End If
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_btnRunQuery_Click:
Exit Sub
Err_btnRunQuery_Click:
MsgBox Err.Description
Resume Exit_btnRunQuery_Click
End Sub
In your query, set the selection criterion for Field1 to be:
GetParameter1()
How this works:
1. You start with the parameter set to e.g. HIGH.
2. The DCount function counts records in your table which contain HIGH in Field1. Note that this is counting the Key Field - RecordID in my example - because every record must have a value in its key field.
3. If there is at least one record with HIGH in Field1, the parameter is not altered. In this case, the query displays the records containing HIGH.
4. If DCount returns zero - there are no records containing HIGH in field 1. The VBA code then sets the parameter to SMS.
5. The query then lists records with SMS in Field1.
6. You need the
GetParameter1 function because you can't set a query criterion equal to a Visual Basic variable directly.
7. In your real application, you could set the two search values from text boxes or list boxes on your form, e.g.
Code:
strParameter1 = Me.txtTextBox1
I hope that this helps.
Bob Stubbs