Multiple Criteria DLookUp Error

[JPed]

Hi everyone,

I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.

Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").

I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:

Afterupdate of main order number:

Private Sub OrderNumbertxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")
End Sub

Afterupdate of suffix:

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]")
End Sub

However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub

I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.

Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.

Any help would be appreciated!

 

[nanscombe]

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub

I think you are missing a quote here and there. Would it not be ...

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = [COLOR="Red"]" &[/COLOR] Forms![**INPUT]![SuffixTxt] [COLOR="red"]&[/COLOR] ") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub

 

[Galaxiom]

There are multiple ways to look at the error.

In the first part of the expression you have "Forms![**INPUT]![SuffixTxt]" passed in the string where it will be interpreted later. That is fine.

In the second half you have the value from Forms![**INPUT]![OrderNumbertxt] concatenated.

It would be unusual to combine them like that but either way the "And" has ended up orphaned.

Try this:

ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] =" & Forms![**INPUT]![SuffixTxt]) & " And ([job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")

BTW Using special characters in object names is a a really bad idea.

 

[JPed]

There are multiple ways to look at the error.

In the first part of the expression you have "Forms![**INPUT]![SuffixTxt]" passed in the string where it will be interpreted later. That is fine.

In the second half you have the value from Forms![**INPUT]![OrderNumbertxt] concatenated.

It would be unusual to combine them like that but either way the "And" has ended up orphaned.

Try this:

ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] =" & Forms![**INPUT]![SuffixTxt]) & " And ([job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")

BTW Using special characters in object names is a a really bad idea.

I tried that and it gave me an error of it missing a bracket so after moving things around a bit managed to make it work with this:

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] =" & Forms![**INPUT - Theta A]![SuffixTxt]) & " And ([job] = '" & Forms![**INPUT - Theta A]![OrderNumbertxt] & "')")
End Sub

Thanks so much for the quick help!