Help with DLookup Where clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnB - 02 Mar 2005 13:31 GMT

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

Reply to this Message

Ken Snell [MVP] - 02 Mar 2005 14:50 GMT

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

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

Signature

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
[quoted text clipped - 17 lines]
>
> Cheers, JohnB

Reply to this Message

JohnB - 02 Mar 2005 21:10 GMT

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?
[quoted text clipped - 24 lines]
>
>.

Reply to this Message

JohnB - 03 Mar 2005 10:28 GMT

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?
[quoted text clipped - 24 lines]
>
>.

Reply to this Message

John Vinson - 03 Mar 2005 18:15 GMT

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

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]

Reply to this Message

JohnB - 03 Mar 2005 11:02 GMT

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?
[quoted text clipped - 24 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 03 Mar 2005 13:52 GMT

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))

Signature

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
[quoted text clipped - 55 lines]
>>
>>.

Reply to this Message

JohnB - 03 Mar 2005 14:14 GMT

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
[quoted text clipped - 72 lines]
>
>.

Reply to this Message