DLookup problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 17 Jul 2007 16:44 GMT

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

Reply to this Message

Brad - 17 Jul 2007 17:14 GMT

I figured it out.

Single Quotes

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

> Thanks for taking the time to read my question
>
[quoted text clipped - 24 lines]
>
> Brad

Reply to this Message

fredg - 17 Jul 2007 17:34 GMT

> Thanks for taking the time to read my question
>
[quoted text clipped - 24 lines]
>
> 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] & "'")

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message