Reputation: 572
I am trying to .FindLast to search for a specific record, and it was working with one criteria, but when I tried to use .FindLast with multiple criteria it stopped working. However, I use almost the same statement with .FindFirst and it works which is why I am confused.
The error I get is "Data type mismatch in criteria expression". And the error is for this line: rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"). I stepped through my code and the line .FindFirst ("DONOR_CONTACT_ID= 'strDonor1' and ORDER_NUMBER= 'strOrderNum1'") works correctly however.
Option Compare Database
Option Explicit
Public dbs As DAO.Database
Public rst As DAO.Recordset
Public rstOutput As DAO.Recordset
'Defines DAO objects
Public strDonor1 As Variant
Public strDonor2 As Variant
Public strRecip1 As Variant
Public strRecip2 As Variant
Public strOrderNum1 As Variant
Public strOrderNum2 As Variant
Public strLastDonor As Variant
Function UsingTemps()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_RECIPIENT_SORT", dbOpenDynaset)
'rst refers to the table T_RECIPIENT_SORT
Set rstOutput = dbs.OpenRecordset("T_OUTPUT", dbOpenDynaset)
'rstOutput refers to the table T_OUTPUT
rst.MoveFirst
'first record
strDonor1 = rst!DONOR_CONTACT_ID
'sets strTemp1 to the first record of the DONOR_CONTACT_ID
strRecip1 = rst!RECIPIENT_CONTACT_ID
strOrderNum1 = rst!ORDER_NUMBER
rst.MoveNext
'moves to the next record
Do While Not rst.EOF
'Loop while it's not the end of the file
strDonor2 = rst!DONOR_CONTACT_ID
'strTemp2 = DONOR_CONTACT_ID from T_RECIPIENT_SORT
strRecip2 = rst!RECIPIENT_CONTACT_ID
strOrderNum2 = rst!ORDER_NUMBER
'Sets strRecip = RECIPIENT_CONTACT_ID FROM T_RECIPIENT_SORT
With rstOutput
'Uses T_OUTPUT table
If (strDonor1 = strDonor2) And (strOrderNum1 = strOrderNum2) Then
'Runs if temps have same DONOR_CONTACT ID
If .RecordCount > 0 Then
'If table has records then you can check
rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'")
strLastDonor = rst!RECIPIENT_CONTACT_ID
If strLastDonor = strRecip2 Then
Call LastDonor
Else
Call FirstDonor
End If
Else
'No records in T_Output so needs to add first record
.AddNew
!DONOR_CONTACT_ID = strDonor1
!RECIPIENT_1 = strRecip1
!ORDER_NUMBER = strOrderNum1
.Update
End If
Else
.FindFirst ("DONOR_CONTACT_ID= 'strDonor1' and ORDER_NUMBER= 'strOrderNum1'")
If .NoMatch Then
.AddNew
!DONOR_CONTACT_ID = strDonor1
!RECIPIENT_1 = strRecip1
!ORDER_NUMBER = strOrderNum1
.Update
End If
End If
End With
'Slides variables down
rst.FindFirst "[RECIPIENT_CONTACT_ID] = " & strRecip2
strDonor1 = strDonor2
strRecip1 = strRecip2
strOrderNum1 = strOrderNum2
rst.MoveNext
Loop
Call LastRecord
Set dbs = Nothing
Set rst = Nothing
Set rstOutput = Nothing
End Function
EDIT:
I just added the following code:
Dim strFind As Variant
strFind = "DONOR_CONTACT_ID= '" & strDonor1 & "' AND ORDER_NUMBER= '" & strOrderNum1 & "'"
Debug.Print strFind
rst.FindLast strFind
It displayed this with the Debug.Print:
DONOR_CONTACT_ID= '10136851341' AND ORDER_NUMBER= '112103071441001'
These are the correct values for DONOR_CONTACT_ID and ORDER_NUMBER but I am getting the error "Data type mismatch in criteria expression" with the line rst.FindLast strFind. Could it possibly be because I defined my variables as variants? In the table I have DONOR_CONTACT_ID defined as Decimal with 11 precision, RECIPIENT_CONTACT_ID defined as Decimal with 11 precision, and ORDER_NUMBER as Decimal with 15 precision. I then define the variables in my code as variants. Do you think there could be a problem with this?
Upvotes: 1
Views: 11400
Reputation: 97131
I think your trouble-shooting efforts will be easier if you change this ...
rst.FindLast ("DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'")
to something like this ...
Dim strFind As String
strFind = "DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"
Debug.Print strFind
rst.FindLast strFind
When the code throws an error, or simply doesn't find what you expect, go to the Immediate window (Ctrl+g) and inspect the output from Debug.Print strFind
. You may spot the problem immediately. If not, copy the Debug.Print
output, open a new query in the query designer, switch to SQL View and use the copied text in a WHERE
clause. In this case, I think the query SQL could be:
SELECT *
FROM T_RECIPIENT_SORT
WHERE yadda_yadda;
Replace yadda_yadda with the text you copied from the Immediate window.
That was more like general trouble-shooting advice. For this specific problem, I think you're building the Find
text to include the names of variables instead of those variables' values. See what you get when you Debug.Print
these 2 string expressions.
"DONOR_CONTACT_ID= 'strDonor1' AND ORDER_NUMBER= 'strOrderNum1'"
"DONOR_CONTACT_ID= '" & strDonor1 & "' AND ORDER_NUMBER= '" & strOrderNum1 & "'"
Your code used the first, but I think you actually need the second.
In the update to your question you reported DONOR_CONTACT_ID
and ORDER_NUMBER
are both numeric data types. In that case do not quotes those search values in the Find
string.
"DONOR_CONTACT_ID= " & strDonor1 & " AND ORDER_NUMBER= " & strOrderNum1
Upvotes: 4
Reputation: 178
Could we have some missing data where the DONOR_CONTACT_ID matches but the ORDER_NUMBER is Null? I think Access would throw the kind of error you are getting from that situation.
Won't happen on FindFirst unless the first occurence is the culprit.
Upvotes: 0