DLookup Multiple Criteria Runtime Error

1
votes

I am trying to create a DLookup with multiple criteria in Access 2010, and running into a little trouble. I create invoices via a form. On the invoice form I select the AccountID, and set billing month and year. Based on that information, I would like to search my Prepayment query (quePrepayment) and pull in any prepayments that match those three criteria.

I am currently getting this error:

Run-time error '3075': Syntax error (missing operator) in query expression 'AccountID= & Forms![frmInvoices]!AccountID & Billing_Month = & Forms![frmInvoices]!Billing_Month & Billing_Year = & Forms![frmInvoices]!Billing_Year)'

Private Sub AccountID_Change()
Billing_Prepayment = DLookup("Total_Prepayment", "quePrepayment", "[AccountID] = & Forms![frmInvoices]!AccountID And [Billing_Month] = & Forms![frmInvoices]!Billing_Month And [Billing_Year] = & Forms![frmInvoices]!Billing_Year")
End Sub
1
ms-accessvbaruntime-error

1 Answers

0
votes

Make the third DLookup argument a string with the Forms![frmInvoices] control references built into it. (The db engine can de-reference those controls when it evaluates the expression.)

Billing_Prepayment = DLookup("Total_Prepayment", "quePrepayment", _
    "[AccountID] = Forms![frmInvoices]!AccountID And [Billing_Month] = Forms![frmInvoices]!Billing_Month And [Billing_Year] = Forms![frmInvoices]!Billing_Year")

However, that string is so long it may be challenging to see whether it is built correctly. You can use an approach like this instead ...

Dim strCriteria As String
strCriteria = "[AccountID] = Forms![frmInvoices]!AccountID " & _
    "And [Billing_Month] = Forms![frmInvoices]!Billing_Month " & _
    "And [Billing_Year] = Forms![frmInvoices]!Billing_Year")
Debug.Print strCriteria
Billing_Prepayment = DLookup("Total_Prepayment", "quePrepayment", _
    strCriteria)

Then in case of trouble, you can go to the Immediate window (Ctrl+g) to examine what was built for strCriteria.