DLookup problems

[Brad]

Thanks for taking the time to read my question

Dim CellID as Variant 'When the DLookup works I'll change this to Integer
Dim ThePhoneNumber as String

CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber = 555-555-5555")

returns Null

The value is there.

This works:
SELECT tblCellInfo.CellID, tblCellInfo.CellNumber
FROM tblCellInfo
WHERE (((tblCellInfo.CellNumber)="555-555-5555"));

I'd like to get this to work:

ThePhoneNumber = "555-555-5555"

CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber = " & """" &
ThePhoneNumber & """")

What is wrong with my DLookup??? Both make CellID = Null

Thanks,

Brad

 

[Brad]

I figured it out.

Single Quotes

CellID = DLookup("CellID", "tblCellInfo", "CellNumber = '" & ThePhoneNumber
& "'")

 

[fredg]

Thanks for taking the time to read my question

Dim CellID as Variant 'When the DLookup works I'll change this to Integer
Dim ThePhoneNumber as String

CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber = 555-555-5555")

returns Null

The value is there.

This works:
SELECT tblCellInfo.CellID, tblCellInfo.CellNumber
FROM tblCellInfo
WHERE (((tblCellInfo.CellNumber)="555-555-5555"));

I'd like to get this to work:

ThePhoneNumber = "555-555-5555"

CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber = " & """" &
ThePhoneNumber & """")

What is wrong with my DLookup??? Both make CellID = Null

Thanks,

Brad

Well, if you already know the CellNumber is 555-555-5555 why do you
need to look it up? [CellID] = "555-555-5555" :-(

Anyway, it seems that the field [CellNumber] is a text datatype field,
in which case the number to be looked up must be enclosed within
quotes (you did this in your Select Where clause, ...
WHERE (((tblCellInfo.CellNumber)="555-555-5555")); ... why not here?).

Since the entire where clause in the DLookUp is within quotes, you use
single quotes around the string.

CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber =
'555-555-5555'")

If the actual number is unknown, but is showing on a control on the
current record on the form, then you would use:
CellID = DLookup("CellNumber", "tblCellInfo", "CellNumber = '" &
Forms!NameOfForm!![ControlOnFormName] & "'")