Find first won't find

[lawdy]

I need to find a record in a table that is greater than the low number and higher or equals the high number. The folowing table is anexample.



strTaxType fDblLowAmt fDblHighAmt
MarriedWeekly 0 163
MarriedWeekly 163 512
MarriedWeekly 512 1583
MarriedWeekly 1583 3029


My code is as follows:

.FindFirst "[fTxtTaxType] = '" & strTaxType & "'" & " And " _
& "[fDblLowAmt] > " & dblTotalPay & " And " _
& "[fDblHighAmt] <= " & dblTotalPay
If Not .NoMatch Then
It does not locate a record where strTaxType = "MarriedWeekly" and dblTotalPay = 600, or any other amount.
Thanks in advance.

[ItsMe]

Are you using "with"? Do you get an error?

Also, maybe you can try
If .NoMatch = False Then

[lawdy]

I tried If .NoMatch = False Then That did not help and yes it is inside a with statement. More of the code follows:

With TRrst
.FindFirst "[fTxtTaxType] = '" & strTaxType & "'" & " And " _
& "[fDblLowAmt] > " & dblTotalPay & " And " _
& "[fDblHighAmt] <= " & dblTotalPay
If .NoMatch = False Then
'
dblAllowance = ![fDblAllowance]
dblMinTax = ![fDblMinTax]
dblAdditionalPercent = ![fDblAdditionalPercent]
dblLowAmt = ![fDblLowAmt]
MsgBox "dblAllowance " & dblAllowance
End If
End With

Thanks for trying

[ItsMe]

What about something like this. You can check the immediate window to check your SQL syntax and verify your double variant.

Dim strWhere As String
strWhere = "[fTxtTaxType] = '" & strTaxType & "'" & " And " _
         & "[fDblLowAmt] > " & dblTotalPay & " And " _
         & "[fDblHighAmt] <= " & dblTotalPay
Debug.Print strWhere & vbCrLf & "*****************************" & vbCrLf
Debug.Print "dblTotalPay = " & dblTotalPay
With TRrst
.FindFirst strWhere
    
    If .NoMatch = False Then
    
        MsgBox "Found a match"
        
        dblAllowance = ![fDblAllowance]
        dblMinTax = ![fDblMinTax]
        dblAdditionalPercent = ![fDblAdditionalPercent]
        dblLowAmt = ![fDblLowAmt]
        MsgBox "dblAllowance " & dblAllowance
    
    Else
    
        MsgBox "Did not find a match"
    
    End If
End With

[lawdy]

I pasted that in and got the following:
MsgBox "Did not find a match"
[fTxtTaxType] = 'MarriedWeekly' And [fDblLowAmt] > 686 And [fDblHighAmt] <= 686

[ItsMe]

Nothing is jumping out at me. When I have more than one Column in the where criteria I usualy include the where criteria within the OpenRecordset statement. Maybe you can try that..... do a .movelast and get a recordcount.

Other than that, build a query using the query builder to get your where clause correct. Maybe it has something to do with the data and the query itself. The query builder may illustrate a mistake.

[June7]

Not sure your logical operators are correct.

Do you want records where dblTotalPay is between [fDblLowAmt] and [fDblHighAmt]?

If BETWEEN AND won't work then try:

& dblTotalPay & ">= [fDblLowAmt] And " & dblTotalPay & "<= [fDblHighAmt]"

[lawdy]

You are right June 7, my logical operators were reversed. It works every time now. It probably would have been better ti use BETWEEN AND. Thank you very much.