Syntax on Multiple Criteria in Where Statement (1 Viewer)

[Adam McReynolds]

I need help to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.

[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.

Dim maxFlag As String
Dim flagCriteriaWarranty As String
Dim flagCriteriaEstimate As String
Dim strWhere As String
Dim verifyWarrnty As String
Dim priceAdjustment As String
Dim noStatement As String

verifyWarranty = "Verify Warranty"
priceAdjustment = "Price Adjustment"
noStatement = "No"

flagCriteriaWarranty = "WarrantyStatusFlag = '" & verifyWarranty & "' And WarrantyFlagCleared = '" & noStatement & "'"
flagCriteriaEstimate = "EstimateFlag = '" & priceAdjustment & "' And EstimateFlagCleared = '" & noStatement & "'"

strWhere = " & flagCriteriaWarranty & " Or " & flagCriteriaEstimate & "

maxFlag = DMax("prikey", "tbl_module_repairs", strWhere)

If maxFlag > 0 Then
DoCmd.OpenForm "FRM_RF_FLAG_MAIN", acNormal, , "prikey = " & maxFlag & ""
Else
MsgBox "Contact the Database Administrator"
End If

Any help would be much appreciated.

 

[BlueIshDan]

why are you breaking it up into so many variables

 

[BlueIshDan]

strWhere = flagCriteriaWarranty & " Or " & flagCriteriaEstimate

 

[MarkK]

Your code is unnecessarily verbose, hang on, I'll refactor it . . .

 

[plog]

Your strWhere makes no sense when used in your DMAX call:

strWhere = " & flagCriteriaWarranty & " Or " & flagCriteriaEstimate & "

Everything inside quote marks gets passed as is, everything outside of quote marks gets evaluated as a variable. The way your quote marks are set up, you are not using any variables in strWhere. The only value outside of the quote marks is the 'OR' which makes no sense, since its not a variable.

Once you get that fixed and strWhere doesn't have any syntax errors, you are still going to not get the results you want. You are going to need parenthesis around the parts of your criteria to seperate the two portions seperated by that 'OR'.

My advice is to play with it until you get strWhere to evaluate how you want it to--this means spitting it out somehow so you can actually read with your eyes the string it contains.

 

[MarkK]

There.

    Dim criteria As String
    Dim maxFlag As String
    
    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = 'No') Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = 'No')"
    
    maxFlag = DMax("prikey", "tbl_module_repairs", criteria)

    If maxFlag > 0 Then
        DoCmd.OpenForm "FRM_RF_FLAG_MAIN", acNormal, , "prikey = " & maxFlag & ""
    Else
        MsgBox "Contact the Database Administrator"
    End If

I'm not saying there aren't still errors in there, but if your code is not in it's simplest form, you make your life harder for yourself.

 

[Adam McReynolds]

why are you breaking it up into so many variables

Because when I just put the string into the statement I got errors. So I just turned them into string variables so that I could write it w/o it turning red. I know its bad.

 

[Adam McReynolds]

strWhere = flagCriteriaWarranty & " Or " & flagCriteriaEstimate

I tried this, I actually originally had this, but I still get data mismatch in criteria. Thanks for the start.

 

[Adam McReynolds]

Your strWhere makes no sense when used in your DMAX call:

strWhere = " & flagCriteriaWarranty & " Or " & flagCriteriaEstimate & "

Everything inside quote marks gets passed as is, everything outside of quote marks gets evaluated as a variable. The way your quote marks are set up, you are not using any variables in strWhere. The only value outside of the quote marks is the 'OR' which makes no sense, since its not a variable.

Once you get that fixed and strWhere doesn't have any syntax errors, you are still going to not get the results you want. You are going to need parenthesis around the parts of your criteria to seperate the two portions seperated by that 'OR'.

My advice is to play with it until you get strWhere to evaluate how you want it to--this means spitting it out somehow so you can actually read with your eyes the string it contains.

Thanks for the reply. Yea, I have tried at least to my knowledge every iteration I know of. What I posted was just where I ended. I just kept getting an error, so I thought I would post here. Thanks again.

 

[Adam McReynolds]

Thanks for the reply. I still get a data mismatch error in criteria though. I know I had fits before with the [prikey] being an autonumber, to the point where when I asked for help on that problem originally, even the normal number syntax answers did not fix it. Could this autonumber field be the problem? Thanks again.

There.

    Dim criteria As String
    Dim maxFlag As String
    
    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = 'No') Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = 'No')"
    
    maxFlag = DMax("prikey", "tbl_module_repairs", criteria)

    If maxFlag > 0 Then
        DoCmd.OpenForm "FRM_RF_FLAG_MAIN", acNormal, , "prikey = " & maxFlag & ""
    Else
        MsgBox "Contact the Database Administrator"
    End If

I'm not saying there aren't still errors in there, but if your code is not in it's simplest form, you make your life harder for yourself.

 

[JHB]

Do you have lookup fields in your table, (then I think you problem is that)?
If you think it is the autonumber field who cause the error then only for error finding, copy the table and change the autonumber field!

 

[MarkK]

Yeah, this

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = 'No') Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = 'No')"

should almost certainly be amended to

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = False) Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = False)"

or clearer still, to me

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And Not WarrantyFlagCleared) Or " & _
        "(EstimateFlag = 'Price Adjustment' And Not EstimateFlagCleared)"

 

[BlueIshDan]

NO Markk YOU didn't fix my problem. YOUR PRACTICES WILL BE DISMISSED! lol

 

[Adam McReynolds]

Yeah, this

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = 'No') Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = 'No')"

should almost certainly be amended to

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And WarrantyFlagCleared = False) Or " & _
        "(EstimateFlag = 'Price Adjustment' And EstimateFlagCleared = False)"

or clearer still, to me

    criteria = _
        "(WarrantyStatusFlag = 'Verify Warranty' And Not WarrantyFlagCleared) Or " & _
        "(EstimateFlag = 'Price Adjustment' And Not EstimateFlagCleared)"

Thanks, the false did it. Thanks for the help.