Help with DLookup Where clause

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same value in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB
 
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])
 
Yes Ken, thats correct. Sorry if I didnt explain better.
Thanks for the reply. Ill try it tomorrow around 10.30 GMT
and will get back to you. Cheers, JohnB
-----Original Message-----
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])


--

Ken Snell
<MS ACCESS MVP>

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same value in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB
Click to expand...


.
Click to expand...
 
Hi again Ken. Ive tried this and it gives me Error in the
field. Ive had a play around with it and noticed that,
perhaps, the last quote character is in the wrong place.
To test it a bit I moved the quote character and then
changed the code to the following, using an actual value
for SecondLandlady, and this works OK

=DLookup ("FirstName","tblLandladies","[LandladyID]= 412")

But when I use this, I get Error

=DLookup ("FirstName","tblLandladies","[LandladyID]= &
[SecondLandladyID]")

So the refererence to the SecondLandlady field, which is
in the current record being displayed, is wrong somehow.
Can you help further please?

Thanks again, JohnB

-----Original Message-----
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])


--

Ken Snell
<MS ACCESS MVP>

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same value in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB
Click to expand...


.
Click to expand...
 
Hi again Ken. Ive played around some more and it seems
that the last quote character is in the correct place
after all but it seems that the last reference should be
to the field name on the form, not the field name in the
table. Perhaps Im just not explaining very well.

Anyway, Im using a combo to select the SecondLandlady and
the following now works but with one further slight
problem.

=DLookUp("FirstName","tblLandladies","[LandladyID] = " &
[cboSecondLandlady])

The problem I now have is that Error appears in the field
whenever I have not selected a Landlady name in the
combo.

Is there some way I can stop Error appearing when the
combo is empty?

Thanks again, JohnB
-----Original Message-----
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])


--

Ken Snell
<MS ACCESS MVP>

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same value in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB
Click to expand...


.
Click to expand...
 
Yes, the criterion expression needs to use the form's control as the source
of the data being used to filter the lookup. I didn't know what that was
when I posted the suggested solution, and it appears that cboSecondLandlady
is the name of that control.

When the combo box is empty, you can use the Nz function to substitute the
Null value from the combo box with a "dummy" value so that the DLookup
function will not error. For example, let's assume that all your landladyid
values are positive (greater than zero). You could use a value of 0 or any
negative number so that a "blank" value will show in the textbox.

=DLookUp("FirstName","tblLandladies","[LandladyID] = " &
Nz([cboSecondLandlady], -99999))


--

Ken Snell
<MS ACCESS MVP>


JohnB said:
Hi again Ken. Ive played around some more and it seems
that the last quote character is in the correct place
after all but it seems that the last reference should be
to the field name on the form, not the field name in the
table. Perhaps Im just not explaining very well.

Anyway, Im using a combo to select the SecondLandlady and
the following now works but with one further slight
problem.

=DLookUp("FirstName","tblLandladies","[LandladyID] = " &
[cboSecondLandlady])

The problem I now have is that Error appears in the field
whenever I have not selected a Landlady name in the
combo.

Is there some way I can stop Error appearing when the
combo is empty?

Thanks again, JohnB
-----Original Message-----
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])


--

Ken Snell
<MS ACCESS MVP>

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same value in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you very much Ken. That got it perfectly. Thanks
for staying with me on this one. Cheers, JohnB
-----Original Message-----
Yes, the criterion expression needs to use the form's control as the source
of the data being used to filter the lookup. I didn't know what that was
when I posted the suggested solution, and it appears that cboSecondLandlady
is the name of that control.

When the combo box is empty, you can use the Nz function to substitute the
Null value from the combo box with a "dummy" value so that the DLookup
function will not error. For example, let's assume that all your landladyid
values are positive (greater than zero). You could use a value of 0 or any
negative number so that a "blank" value will show in the textbox.

=DLookUp("FirstName","tblLandladies","[LandladyID] = " &
Nz([cboSecondLandlady], -99999))


--

Ken Snell
<MS ACCESS MVP>


Hi again Ken. Ive played around some more and it seems
that the last quote character is in the correct place
after all but it seems that the last reference should be
to the field name on the form, not the field name in the
table. Perhaps Im just not explaining very well.

Anyway, Im using a combo to select the SecondLandlady and
the following now works but with one further slight
problem.

=DLookUp("FirstName","tblLandladies","[LandladyID] = " &
[cboSecondLandlady])

The problem I now have is that Error appears in the field
whenever I have not selected a Landlady name in the
combo.

Is there some way I can stop Error appearing when the
combo is empty?

Thanks again, JohnB
-----Original Message-----
I take it that this is the control source for the textbox in that subform?

=DLookup ("FirstName","tblLandladies","[LandladyID]=" & [SecondLandladyID])


--

Ken Snell
<MS ACCESS MVP>

Hi. Can anyone help me put together a Where clause for
the following DLookup please? I will use this in a text
box in a Subform called frmLandladiesSubform.

=DLookup ("[FirstName]","[tblLandladies]","Where Clause")

I want the Where clause to get access to go to
tblLandladies, find the record that has the same
Click to expand...
value
in
field LandladyID as field SecondLandladyID in the record
currently being displayed in frmLandladiesSubform. Then
display the content of the FirstName field.

So, say the current record being displayed in
frmLandladiesSubform has a SecondLandladyID value of 344,
then access would go to tblLandladies, find the record
whose LandladyID value is also 344 and display the value
of field FirstName in the text box.

Thanks in advance for any help.

Cheers, JohnB




.
Click to expand...
Click to expand...


.
Click to expand...
 
=DLookup ("FirstName","tblLandladies","[LandladyID]= &
[SecondLandladyID]")
Click to expand...

Should be

=DLookup ("FirstName","tblLandladies","[LandladyID]= " &
[SecondLandladyID])

You're concatenating two strings - the constant

[LandladyID] =

and the variable [SecondLandladyID]. If that field contains 312, the
result will be

[LandladyID] = 312

and DLookUp will find her.

John W. Vinson[MVP]
 
Back
Top