DLookUp (I don´t get it!)

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!

I have tryed several syntax for this DLookUp (see below) but I keep on
getting "Null" as a result. I´m running this DLookUp in the direct window
(A2002).

DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "[TypeOfPlace]='" & Hem &
"'")
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "[TypeOfPlace]=" & "Hem")

strHome = "Hem"
intPhoneTypeHomeID = DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace",
"[TypeOfPlace]= " & strHome)

What am I doing wrong?

TIA!
// Niklas
 
Dlookup has a few problems firs
if no record is found it returns a null
second it only returns the first occurrence if more that one field meets the criteri
*Note if you field [TypeOfPlace] is a text box then you need the extra "'" other wise no nee
try this for your cod
****CODE START**
Dim tempstring as varian
tempstring = DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "[TypeOfPlace]='" & Hem & "'"
if isnull(tempstring) the
intPhoneTypeHomeID = "Nothing
els
intPhoneTypeHomeID = tempstrin
end i
****CODE END****
Hope this help
B
 
Thanks BB!

I still don´t get it because field <TypeOfPlaceID> in table
<tblLookUpTypeOfPlace> is a auto number field so there is a value. And I DO
have the value "Hem" in field <TypeOfPlace> in the same table.

If I use DLookUp to lookup the string "Hem" using the auto number as a
criteria then it workes (returns value "Hem"). Like this:

DLookup("[TypeOfPlace]", "tblLookUpTypeOfPlace", "[TypeOfPlaceID]= 1")
Hem

There must be some error in my syntax, or?

TIA!

// Niklas
 
Try thi
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "[TypeOfPlace]=" & chr(34) & "Hem" & chr(34)

or this (only for this case
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "Left([TypeOfPlace],3)=" & chr(34) & "Hem" & chr(34)

or thi
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "Ucase([TypeOfPlace])=" & chr(34) & "HEM" & chr(34)

Good Luck !!

Ricard
 
Thanks a lot Ricardo!

It workes just like a train! :-)

// Niklas


Ricardo(uy) said:
Try this
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "[TypeOfPlace]=" & chr(34) & "Hem" & chr(34))

or this (only for this case)
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace",
Click to expand...
"Left([TypeOfPlace],3)=" & chr(34) & "Hem" & chr(34))
or this
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "Ucase([TypeOfPlace])=" & chr(34) & "HEM" & chr(34))

Good Luck !!!

Ricardo
Click to expand...
 
Is it possible that TypeOfPlaceID was set up as a lookup field in
tblLookUpTypeOfPlace? Autonumber fields can't be lookup fields, but
perhaps the field is a foreign key in the table you are trying to
DLookup from, and is type Number there. Go to table design view,
highlight the field, and see if there is anything in the "Lookup" tab
to find out.

If it is an lookup field, then, when you browse the table you will see
e.g. "Hem", although the stored value is actually 1. If you make a
query with "Hem" as the criteria you should see the same problem - no
results.

Most of the Access experts I've encoutered recommend that lookup
fields not be used because of this type of confusion.
 
Back
Top