Run time error in multiple criteria DLookup (1 Viewer)

[jibb]

I have got the following lookup on my form:

Me.PALLET_QUANTITY = DLookup("[PALLET QTY]", "tblSupplierPalletQty", "[STOCK CODE]=" & Forms![ENTER PURCHASE ORDER]![PRODUCT CODE] & " And [SUPPLIER]=" & Forms![ENTER PURCHASE ORDER]![ACC NO] & "")

to find the pallet quantity of a product for a specific supplier, but every time I run it, it comes up with the following error -

"Run-time error '3075':

Syntax error (missing operator0 in query expression '[STOCK CODE]=112204CBC And [SUPPLIER]=HAREUR'. "

Any ideas why??

 

[sparks80]

Hi,

Try putting single quotation marks around the text string you are entering for the supplier like this:

Me.PALLET_QUANTITY = DLookup("[PALLET QTY]", "tblSupplierPalletQty", "[STOCK CODE]=" & Forms![ENTER PURCHASE ORDER]![PRODUCT CODE] & " And [SUPPLIER]=[SIZE=3][COLOR=magenta]'[/COLOR][/SIZE]" & Forms![ENTER PURCHASE ORDER]![ACC NO] & "[SIZE=3][COLOR=magenta]'[/COLOR][/SIZE]")

 

[jibb]

That returns the same error but with an ' in front of the "HAREUR" in the error.

It doesn't seem to be picking up the "and" as an operator.

 

[spikepl]

You have put 2 things together, and you don't know whether it is one, or the other or the combination that does not work. To find out, make a dlookup just depending on one criterion, make it work. Then the other. Once the two each do their job, then combine. This way you always know what failed.

This is a very common error here: ppl put complex things together and are lost, when it doesn't work. ONE STEP AT A TIME will save you lots of grief.

 

[jibb]

Ok!

I have made each half of the formula work and put them together, but it now just says "Run-time error 13: type mismatch"

 

[spikepl]

Oh yeah. "I have done ¤"#&%#¤"&#"#&¤!" and it doesn't work:

ALways show the code, because that will always be the next question.

 

[jibb]

OK, here you go...

Me.PALLET_QUANTITY = DLookup("[PALLET QTY]", "tblSupplierPalletQty", "[STOCK CODE]= '" & Forms![ENTER PURCHASE ORDER]![PRODUCT CODE] & "'" And "[SUPPLIER]='" & Forms![ENTER PURCHASE ORDER]![ACC NO] & "'")

Each of the criteria works on its own, it's probably a simple mistake!

 

[VilaRestal]

Perhaps do

Dim strWhere as String
strWhere = "[STOCK CODE]= '" & Forms![ENTER PURCHASE ORDER]![PRODUCT CODE] & "'" And "[SUPPLIER]='" & Forms![ENTER PURCHASE ORDER]![ACC NO] & "'"
debug.print strWhere
Me.PALLET_QUANTITY = DLookup("[PALLET QTY]", "tblSupplierPalletQty", strWhere)

so you can see in the immediate window what that where clause looks like.

You could then copy it and append it to a query
SELECT [PALLET QTY] FROM tblSupplierPalletQty WHERE
and see what access says about that query.

 

[VilaRestal]

In fact I can now see the error:

[PRODUCT CODE] & "'" And "[SUPPLIER]

should be

[PRODUCT CODE] & "' And [SUPPLIER]

(Access was trying to binary compare the two strings, getting false and using that false as the where of the dlookup)

 

[sparks80]

Hi,

The AND part of the statement ended up outside the string, try this:

Me.PALLET_QUANTITY = DLookup("[PALLET QTY]", "tblSupplierPalletQty", "[STOCK CODE]='" & Forms![ENTER PURCHASE ORDER]![PRODUCT CODE] & "' And [SUPPLIER]='" & Forms![ENTER PURCHASE ORDER]![ACC NO] & "'")

 

[jibb]

Excellent!! Thanks sparks.

 

[rahulnagar]

Hi,
I am trying to copy data from one sheet to another depending on a certain data match. i am geeting error 438
below is the code
Private Sub CommandButton1_Click()

For x = 8 To 1500
Dim a As Integer
Dim b As String

Dim c As String

a = Sheets("New opportunity").Cells(x, 25)
b = Sheets("New opportunity").cell(x, 1)
If (a < 0) Then
c = b
End If
Sheets("status_report").cell(x, 1) = c




Next x


End Sub