DLookup Syntax error?

[back2basic]

Will someone please tell me what the problem is in this DLookup statement? I keep getting a syntax error on the "Me.cboAsset_ID.Column(6)".

answer = DLookup("[Issued_Quantity]", "Materials", "Material_ID = Me.cboAsset_ID.Column(6)")

 

[pr2-eugin]

back2basic, see if this link helps..

 

[plog]

"Material_ID = Me.cboAsset_ID.Column(6)"


Technically the syntax error is that you are searching by numeric data but you've provided text to it. In that portion of the code you are literally looking for the Material_ID number that equals the text "Me.cboAsset_ID.Column(6)". So either you need to put quote marks around it:

"Material_ID='Me.cboAsset_ID.Column(6)'"

While syntatically that would be correct, it would probably fail because I'm guessing you're ID numbers don't look like that. Most likely you need to remove the variable portion of the criteria ("Me.cboAsset_ID.Column(6)") from the quote marks entirely:

"Material_ID = " & Me.cboAsset_ID.Column(6)

That will search for the Material_ID in Materials that is equal to whatever numeric value is in Me.cboAsset_ID.Column(6). Which may or may not be the correct way to reference whatever control you want to reference. But hey, the syntax will be right.

 

[gemma-the-husky]

couple of other things

assume this is correct

"Material_ID = " & Me.cboAsset_ID.Column(6)

1. a combo box is ZERO based, so column(6) refers to the 7th column in rowsource, not the sixth. if you want the 6th column, you may need column(5)

2. in some cases (I heisitate to say all cases), the column() value will come back as a string, not a number. so you may need to coerce the value to a numeric, if you get a type mismatch error

"Material_ID = " & clng(Me.cboAsset_ID.Column(6))

 

[back2basic]

Plog thank you. You are correct. Removing the variable portion of the criteria works. Gemma thank you for your advice. Pr2 as always you have been a big help for me also.