Dlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ernst Guckel - 29 Mar 2008 04:43 GMT

Hello,

I am having noting but problems with this... I cannot seem to figure out
why I am getting #Error

=DLookUp("[GenderDesc]","ltblGender","[GenderID] = " & [EmpGender])

=DLookUp("[W4StatusDesc]","ltblW4Status","[W4StatusID] = " & [EmpW4Status])

both return #Error but if I enter the same thing in VBA I get the right
answer...

Ernst.

Reply to this Message

fredg - 29 Mar 2008 05:15 GMT

> Hello,
>
[quoted text clipped - 9 lines]
>
> Ernst.

Make sure the name of control (in the report) is NOT the same as the
name of any field used in the control source expression.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Allen Browne - 29 Mar 2008 05:18 GMT

In ltblGender, is GenderID a Number field or a Text field?

If it's Text, you need extra quotes:
=DLookUp("[GenderDesc]", "ltblGender",
"[GenderID] = """ & [EmpGender] & """")
For an explanation, see:
http://allenbrowne.com/casu-17.html

If it is a Number field you would an error if EmpGender is null, since the
3rd argument would end up being just:
[GenderID] =
which is clearly wrong. To fix this error, use Nz() to supply something for
null, e.g.:
=DLookUp("[GenderDesc]", "ltblGender",
"[GenderID] = " & Nz([EmpGender],0))

Whatever EmpGender is, of couse its value has to match the same data type as
the field in the table.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello,
>
[quoted text clipped - 10 lines]
>
> Ernst.

Reply to this Message

Ernst Guckel - 29 Mar 2008 16:06 GMT

Still having the same problem. I have since renamed the control to something
other than the field name (Didn't catch that) but it still returns the same
error...

txtGenderID returns 1 < -- [EmpGender] <-- field name
txtGender with the lookup returns #Error but it should return "Male"

Ernst.

> In ltblGender, is GenderID a Number field or a Text field?
>
[quoted text clipped - 29 lines]
> >
> > Ernst.

Reply to this Message

Allen Browne - 29 Mar 2008 16:21 GMT

Press Ctrl+G to open the Immediate Window.

Work there to build up the expression until you get it right.

Start with:
? DLookUp("GenderDesc", "ltblGender")
That should return a value (though you have not specified which one.)

Then build up the 3rd argument, watching the data types as explained.

If you need more help, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Once you get it right in the Immediate Window, you can then work on getting
it right in the report.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Still having the same problem. I have since renamed the control to
> something
[quoted text clipped - 43 lines]
>> > right
>> > answer...

Reply to this Message

Ernst Guckel - 29 Mar 2008 17:34 GMT

Thanks... got it.

Ernst.

> Press Ctrl+G to open the Immediate Window.
>
[quoted text clipped - 61 lines]
> >> > right
> >> > answer...

Reply to this Message