CODEWITHSUNDEEP
vbams-access
Rob
Rob

Reputation: 538

Type Mismatch in DMax with multiple arguments

So I have a table of invoices ("Invoices") in MS Access. I also have a form displaying each record and for each record that I look at I would like to show a field from the previous invoice of that same customer.

So I tried using the DMax function with 2 arguments. The first to make sure that the invoices were related to the same customer, and the second to make sure that the invoice number was less than the current invoice number being displayed.

RecentInvNo = DMax("[Invoice Number]", "Invoices", "[Invoice Number]<" & Me.Invoice_Number And "[Customer Number] ='" & Me.Combo15 & "'")

Where:
"Invoice Number" is the field in the "Invoices" table.
Me.Invoice_Number is the invoice number on the form.
Me.Combo15 is the customer number on the form.

Unfortunately I get a 'run-time error 13: type mismatch'.

However the following works:

  Temp = DMax("[Invoice Number]", "Invoices", "[Invoice Number]<" & Me.Invoice_Number)

And

RecentInvNo = DMax("[Invoice Number]", "Invoices", "[Customer Number] ='" & Me.Combo15 & "'")

So why am I getting a type mismatch when I combine the two? Does each side of the And need to be the same type? Can anyone suggest a correction or alternative?

Thanks!

Upvotes: 1

Views: 144

Answers (1)

June7
June7

Reputation: 21389

The AND operator is literal text and must be within quote marks. Also, need a concatenation character.

RecentInvNo = DMax("[Invoice Number]", "Invoices", "[Invoice Number]<" & Me.Invoice_Number & " And [Customer Number] ='" & Me.Combo15 & "'")

This assumes [Invoice Number] is a number type field.

Advise not to use spaces nor punctuation/special characters in naming convention.

Upvotes: 1

Related Questions