Dlookup Trouble

[Joe Williams]

I am trying to create a dlookup statement based on the values from forms. If
I hardcode the following statement with the values it works fine:

dlookup("PatientID", "Cases", "CaseID = 2 AND StaffName = 'Administrator'")

The problem comes when I try to refer to an open form for the crieteria
values rather than hardcode them. I am positive it has to do with the
quotation marks and how the criteria portion of the dlookup statement is
contructed but I cannot figure out what I am doing wrong. Here is what I am
trying to do:

DLookup("PatientID", "Cases", "[CaseDetID] = " & forms!Case!CaseID & " and
[StaffName] = '" & forms!Case!Staff & "'")

So bascially I am trying to use two items in the criteria poortion of the
dlookup function, one that is a string and one that is an autonumber key. I
have tried numerous other combinations of quotation marks and such but keep
keeping "Need List Seperator" errors or a Null value when the hard coded
function returns a value

A msgbox of the criteria section of the dlookups I am trying looks correct,
something like CaseID = 2 and StaffName = 'Adminnistrator' is displayed in
the msgbox but I still get errors when used in the dlookup function.

Any ideas on how to move past this issue, or general comments about hw to
deal with constructing these dynamic statements? I am constantly battling
with where to put quotation marks, how many, etc. Thanks

Joe

 

[Rick Brandt]

I am trying to create a dlookup statement based on the values from
forms. If I hardcode the following statement with the values it works
fine:
dlookup("PatientID", "Cases", "CaseID = 2 AND StaffName =
'Administrator'")
The problem comes when I try to refer to an open form for the
crieteria values rather than hardcode them. I am positive it has to
do with the quotation marks and how the criteria portion of the
dlookup statement is contructed but I cannot figure out what I am
doing wrong. Here is what I am trying to do:

DLookup("PatientID", "Cases", "[CaseDetID] = " & forms!Case!CaseID &
" and [StaffName] = '" & forms!Case!Staff & "'")

[snip]

The above looks fine except that your hard-coded expression used "CaseID" and
your second one used "[CaseDetID]". Was that a typo?

 

[Joe Williams]

Yes, a typo. Sorry. Both statement use the same field and tablenames. The
issue lies somewhere in the criteria section of the dlookup. for instance,
if I just do the following:

dlookup("PatientID", "Cases", " 'CaseID = 2 and Staffname = ' &
forms!Case!Staff")

I can get a result. But as soon as I try to substitute the 2 with a form
reference, I run into trouble.

Joe

I am trying to create a dlookup statement based on the values from
forms. If I hardcode the following statement with the values it works
fine:
dlookup("PatientID", "Cases", "CaseID = 2 AND StaffName =
'Administrator'")
The problem comes when I try to refer to an open form for the
crieteria values rather than hardcode them. I am positive it has to
do with the quotation marks and how the criteria portion of the
dlookup statement is contructed but I cannot figure out what I am
doing wrong. Here is what I am trying to do:

DLookup("PatientID", "Cases", "[CaseDetID] = " & forms!Case!CaseID &
" and [StaffName] = '" & forms!Case!Staff & "'")

[snip]

The above looks fine except that your hard-coded expression used "CaseID"
and your second one used "[CaseDetID]". Was that a typo?

 

[Rick Brandt]

Yes, a typo. Sorry. Both statement use the same field and tablenames.
The issue lies somewhere in the criteria section of the dlookup. for
instance, if I just do the following:

dlookup("PatientID", "Cases", " 'CaseID = 2 and Staffname = ' &
forms!Case!Staff")

I can get a result. But as soon as I try to substitute the 2 with a
form reference, I run into trouble.

Then clearly forms!Case!CaseID does not equal 2 when you think it does. Is this
control a ComboBox? If so the bound column might be a different column than the
one with a 2 as its value. Are you using a Lookup field perhaps (really bad)?