Dlookup

  • Thread starter Thread starter Ernst Guckel
  • Start date Start date
E

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.
 
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.
Click to expand...

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

Allen Browne said:
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.

Ernst Guckel said:
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.
Click to expand...
Click to expand...
 
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.

Ernst Guckel said:
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.

Allen Browne said:
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 said:
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...
Click to expand...
Click to expand...
Click to expand...
 
Thanks... got it.

Ernst.

Allen Browne said:
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.

Ernst Guckel said:
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.

Allen Browne said:
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...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top