DLookUp (I don´t get it!)

[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

 

[Guest]

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

 

[Niklas Östergren]

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

 

[Guest]

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

 

[Niklas Östergren]

Thanks a lot Ricardo!

It workes just like a train!

// Niklas

Try this
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 this
DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace", "Ucase([TypeOfPlace])=" & chr(34) & "HEM" & chr(34))

Good Luck !!!

Ricardo

 

[Charles Coleman]

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.