Dlookup

[Ernst Guckel]

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.

 

[fredg]

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.

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.

 

[Allen Browne]

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.

 

[Ernst Guckel]

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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

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.

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 Guckel]

Thanks... got it.

Ernst.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

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.

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