Find Record in Access using .FindFirst with Multiple Criteria

[dloskot]

I have spent several hours trying to debug this code. I have not been able to find the answer anywhere.
I have an Access DataBase I am running Access 2010. I want to search the DB to see if a record with several criteria already exists.
I am able to make it work with a single criteria but as soon as I put in an AND or OR I get one or more errors. In the below code the line with the And in it does not work but if I run either of the two lines below it, it works fine.
I hope you can help me .
Thanks Doug

With KAH_Record
'The first line DOES NOT work, the next two lines do work.  I would like to be able to do
'about 4 different criteria at one.'
    .FindLast "[Payment Method]= '" & PayMethod & "'" And "[EMail]= '" & EMail & "'"
    .FindLast "[Payment Method]= '" & PayMethod & "'"
    .FindLast "[EMail]= '" & EMail & "'"
    
        
        If .NoMatch = False Then
            MsgBox "Found Match"
            'Get_Record_Request = False
            'Exit Function
        Else
        MsgBox "Not Found"
        End If

 

[BigBeachBananas]

Untested code-I think you have to put them all in the same string.

With KAH_Record
    ' Use three conditions with AND
    .FindLast "[Payment Method]='" & PayMethod & "' AND [EMail]='" & EMail & "' AND [SomeOtherField]='" & SomeValue & "'"
    
    If .NoMatch = False Then
        MsgBox "Found Match"
    Else
        MsgBox "Not Found"
    End If
End With

 

[dloskot]

BigBeach: Thank your line worked. I tried to add the third criteria but it is defined as Currency now I keep getting a Runtime error of '6464' Data type mismatch in criteria expression.

.FindLast "[Payment Method]='" & PayMethod & "' AND [EMail]='" & EMail & "' AND [Amount Donated]='" & Amount_Donated & "'"

I assume the ' and " are not quite right.

 

[Micron]

Single or double quotes are text delimiters. Do not use them for numbers or dates. # is for date delimiting only. Try
AND [Amount Donated]=" & Amount_Donated

 

[dloskot]

Micron: Thanks for the explanation about Single, Double quotes and #. I couldn't quite figure them out from all the examples I was looking at.
You suggested change worked. Thanks very much for your your help.
Doug

 

[Micron]

I think you should give BBB the credit. I was going to make the same suggestion as your solution but I was only a close second.
Glad to have helped either way.

 

[dloskot]

Ok one more issue. I added my final Critera which is a date and I keep getting Run-Time Error '13' Type Mismatch.

.FindLast "[Payment Method]='" & PayMethod & "' AND [EMail]='" & EMail _
     & "' AND [Amount Donated]=" & Amount_Donated And "[Event Date]= #" & Evnet_Date & "#"

 

[BigBeachBananas]

Ok one more issue. I added my final Critera which is a date and I keep getting Run-Time Error '13' Type Mismatch.

I see a typo here. Is this intended?

And "[Event Date]= #" & Evnet_Date & "#"

 

[dloskot]

BBB: I just caught this myself. I must have looked at that 20 times before posting. Again thank for your help. This board is fantastic.

 

[dloskot]

Well That didn't fix the issue. I am still getting a Run- Time Error "13 Type Mismatch for the first line in the below code but for the second line it works fine.

.FindLast "[Payment Method]='" & PayMethod & "' AND [EMail]='" & EMail _
     & "' AND [Amount Donated]=" & Amount_Donated And "[Event Date]= #" & Event_Date & "#"
    
     .FindLast "[Event Date]= #" & Event_Date & "#"