Results 1 to 8 of 8
  1. #1
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    175

    Find first won't find

    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.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Are you using "with"? Do you get an error?

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

  3. #3
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    175
    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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    What about something like this. You can check the immediate window to check your SQL syntax and verify your double variant.

    Code:
    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

  5. #5
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    175
    I pasted that in and got the following:
    MsgBox "Did not find a match"
    [fTxtTaxType] = 'MarriedWeekly' And [fDblLowAmt] > 686 And [fDblHighAmt] <= 686

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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]"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    lawdy is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    175
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find
    By sergran in forum Programming
    Replies: 2
    Last Post: 10-08-2013, 01:02 AM
  2. I can't find the bug. Please help
    By sharVyn in forum Programming
    Replies: 5
    Last Post: 08-27-2012, 03:39 PM
  3. Cannot find Page... where is it looking?
    By That_Guy in forum Queries
    Replies: 3
    Last Post: 08-21-2012, 02:54 PM
  4. Find Menu
    By thesugger in forum Access
    Replies: 2
    Last Post: 08-21-2012, 06:19 AM
  5. Find a SUM
    By jcsports31 in forum Access
    Replies: 8
    Last Post: 09-14-2010, 10:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums