Access DLookup Data Mismatch error

Question

I need help. I'm using Access 2007 at my work.

I keep getting the Run-time error 3464: Data type mistmatch in criteria expression. This was working fine and then all of the sudden it wasn't.

I've made some adjustments to my original code but I'm still getting the error.

Please help, this is very frustrating!

Private Sub Loan__AfterUpdate()
Me.Refresh
Dim GetInfoCriteria As String
GetInfoCriteria = "[ACCOUNT_NUMBER] = '" & Screen.ActiveForm.Recordset.Fields("Loan#").Value & "'"
Debug.Print GetInfoCriteria

[Loan#] = DLookup("[ACCOUNT_NUMBER]", "tbl_GetInfo", GetInfoCriteria)

EDIT

Here is my old code that worked for a while:

[Loan#] = DLookup("[ACCOUNT_NUMBER]", "tbl_GetInfo", "[ACCOUNT_NUMBER] = '" & Screen.ActiveForm.Recordset.Fields("Loan#").Value & "'")

I have two table, tbl_GetInfo and tbl_CE. tbl_CE is populated by the user working with a form frm_CE. When the user enters the loan # into frm_CE, I want it to use Dlookup to get this and other information from tbl_GetInfo and put that into tbl_CE. I chose this one field as an example but there are others that it will populate in the same Sub, this is the full example

Dim GetInfoCriteria As String
GetInfoCriteria = "[ACCOUNT_NUMBER] = '" & Screen.ActiveForm.Recordset.Fields("SLSLoan#").Value & "'"
Debug.Print GetInfoCriteria
'
[Loan#] = DLookup("[ACCOUNT_NUMBER]", "tbl_GetInfo", GetInfoCriteria)
[Inv] = DLookup("[INVESTOR_CODE]", "tbl_GetInfo", GetInfoCriteria)
[Name] = DLookup("[BORROWER_PRIMARY_NAME]", "tbl_GetInfo", GetInfoCriteria)
[Status] = DLookup("[LOAN_OTS_OVERALL_STATUS]", "tbl_GetInfo", GetInfoCriteria)

Sorry I hope that helps

Why not Me.[Loan#] ? Why are you including special characters in field / column names? Is Loan# a number or text? — Fionnuala
when you step through the code in debug mode, does the DLookup function return what you expect? Does the [Loan#] control contain what you expect? — Beth
You also appear to be setting a field, control and variable with the same name. — Fionnuala
Sorry to clarify, I'm doing the Dlookup from one table to another. Loan# is a number. — msim
Beth, in the immediate window it prints what I want. The [Loan#] control is entered by the user and should be a loan in our system and therefore work correctly to look up this other information — msim

msim msim · Accepted Answer · 2014-12-12T17:39:47

So I got it to work by writing it like this:

[Loan#] = DLookup("[ACCOUNT_NUMBER]", "tbl_GetInfo", "[ACCOUNT_NUMBER] = " & Screen.ActiveForm.Recordset.Fields("Loan#").Value)

I think it was that the Loan# is a number so even if the other fields are text or number, it needs to be written like a number. Anyway, thanks and sorry it wasn't a very easy to describe problem.

Access DLookup Data Mismatch error

1 Answers