DLookup with textfield in criteria

[smeghead67]

Hello,



I am creating a form that looks up the value of a field on a table that matches two criteria on my form.

My table [partstore] has three fields, [binid], [pid] and [qty] their values are L01-01-03, 2 and 3

my form [takeapart] (For this question) has two text boxes [psid] and [pid]. the values entered into the forms are L01-01-03 and 2

I'm trying the following code in a button,

Dim getbqty As Integer
getbqty = DLookup("[qty]", "[partstore]", "[pid]=[Forms]![takeapart]![pid] AND [binid]='" & [Forms]![takeapart]![psid] & "''")
MsgBox getbqty

When I run this I get "Invalid use of Null"

When I go to immediate mode to debug it, running the dlookup with just the pid = forms pid works but trying the binid to psid comparison always results in a null?

What am i doing wrong?

Ted

[Bob Fitz]

If binid is text try:

Dim getbqty As Integer
getbqty = DLookup("[qty]", "[partstore]", "[pid]=" & [Forms]![takeapart]![pid] & " AND [binid]='" & [Forms]![takeapart]![psid] & "'")
MsgBox getbqty

If binid is numeric try::

Dim getbqty As Integer
getbqty = DLookup("[qty]", "[partstore]", "[pid]=" & [Forms]![takeapart]![pid] & " AND [binid]=" & [Forms]![takeapart]![psid])
MsgBox getbqty

[smeghead67]

Bob,

binid is text. your sample code only changed how it handled the [pid] which is numeric. the way it handled the binid was the same as what i already had. when i check this in immediate mode,

? DLookup("[qty]", "[partstore]", "[pid]= [Forms]![takeapart]![pid]")

will return the first record where the criteria is true. but,

? DLookup("[qty]", "[partstore]", "[binid]='" & [Forms]![takeapart]![psid] & "'")

always returns a null, even though i have copied the values from the table to the form so I know it is there.

odd isn't it?

Ted

[smeghead67]

I believe i have found my problem.

I just tried the following code,

? DLookup("[binid]", "[partstore]")

It returned "L010001"

Wait, that's not the format its saved in... OH

I remembered when i created the table "partstore" i put an Input Mask of !LAA\-00\-00 on the field "binid". I thought it stored the info with the dashes in it because when you look at the table, in datasheet view, the first value of [binid] is L01-00-01.

So, all I had to do was put that input mask on the binid text box of my form and BINGO! it works. Thank you, Bob for helping with an "Idiot Coder Error"

[Bob Fitz]

Glad that I have been of some help. Thanks for posting back with the full solution.