Use one parameter or the other but not both

[gillianleec]

I hope this is a simple question. I am writing a query where I want it to check for the first parameter and if it does not exist, then choose the second parameter. My query returns results for both.

I have tried as my parameters: 1. "High" OR "SMH" 2. IIf(ismissing([Prsched]="High"), "SMH", "High")

Please help.

Gillian

 

[traingamer]

I don't think IsMissing works in queries. You might try:

 IIf(IsNull([Prsched])), "SMH", "High")

but if you post your query, we can probably figure it out.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gillianleec]

The only problem with the isnull function is that there are many more choices besides the "High" and "SMH". I tried IIF(IsNull([PRSCHED]="HIGH"), "SMH", "HIGH") but it didn't work.

Here is my code.



SELECT DISTINCT [PERSON-AR].PTFNAME, [PERSON-AR].PTLNAME, [ITEM-AR].ITTSTCODE, [ITEM-AR].ITPRICE, [VISIT-AR].VTACINTN, [ITEM-AR].ITSRVDT, [VISIT-AR].VTDOCTOR, [PERSON-AR].PTMRN, [VISIT-AR].VTINTN, [STAY-AR].STBILNO, [TEST-AR].TSTDESC, [VISIT-AR].VTPTINTN, [VISIT-AR].VTDEPOT, [VISIT-AR].VTSRVDT, [ITEM-AR].ITPLACE, [VISIT-AR].VTFCLTY, [ITEM-AR].ITUNITS, [ITEM-AR].ITCPTCD, AR_PRICE.PRSCHED, AR_PRICE.PRPRICE
FROM ((([PERSON-AR] INNER JOIN ([STAY-AR] INNER JOIN [VISIT-AR] ON [STAY-AR].STINTN = [VISIT-AR].VTSTINTN) ON [PERSON-AR].PTINTN = [STAY-AR].STPTINTN) INNER JOIN [ITEM-AR] ON ([VISIT-AR].VTPTINTN = [ITEM-AR].ITPTINTN) AND ([VISIT-AR].VTINTN = [ITEM-AR].ITVTINTN)) LEFT JOIN [TEST-AR] ON [ITEM-AR].ITTSTCODE = [TEST-AR].TSTCODE) LEFT JOIN AR_PRICE ON [ITEM-AR].ITTSTCODE = AR_PRICE.PRTSTCODE
WHERE ((([ITEM-AR].ITSRVDT) Between [Forms]![frmA-ARDateRange]![txtBegin] And [Forms]![frmA-ARDateRange]![txtEnd]) AND (([VISIT-AR].VTDEPOT) Like "I*" Or ([VISIT-AR].VTDEPOT)="HH") AND (([VISIT-AR].VTFCLTY)="SMH") AND ((AR_PRICE.PRSCHED)="HIGH" Or (AR_PRICE.PRSCHED)="SMH"));

Gillian

 

[traingamer]

You can't compare the two in one query if the values are actually in separate rows. You need to do some kind of group by in a subquery like: qPrSched

Select AR_PRICE.PRTSTCODE, MIN(AR_PRICE.PRSCHED) as MinPrsched
From AR_PRICE
WHERE ((AR_PRICE.PRSCHED)="HIGH" Or (AR_PRICE.PRSCHED)="SMH")
GROUP by AR_PRICE.PRTSTCODE;

then link on that subquery rather than the AR_PRICE table in your query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[BobStubbs]

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:

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:

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.

    strParameter1 = Me.txtTextBox1

I hope that this helps.

Bob Stubbs

 

[gillianleec]

THANKS!!

Next question. How does the MIN function work? Does it capture the first incident? Am I asking this right?

Gillian

 

[traingamer]

MIN() returns the lowest value. "High" thus comes before "SMH".

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill