GetDesignerAddress() works one way but not the other, please help!

[Gina Whipp]

Hello All,

Little problem, if I type:

WhichDesigner = DLookup("pWhichDivision", "tblProjects", "pID = ProjectID")

doesn't work.

But if I type:

WhichDesigner = DLookup("pWhichDivision", "tblProjects", "pID = 1")

works perfectly. The entire code is below. Can one of you kind gents or
ladies help me out?

Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II



Public Function GetDesignerAddress(ProjectID As Long) As String

Dim WhichDesigner As String
WhichDesigner = DLookup("pWhichDivision", "tblProjects", "pID =
ProjectID")

GetDesignerAddress = DLookup("[cpCompanyName]", "tblCompanyProfile",
"[cpCompanyID]='" & WhichDesigner & "'") & Chr(13) & Chr(10) & _
DLookup("[cpAddress]", "tblCompanyProfile",
"[cpCompanyID]='" & WhichDesigner & "'") & Chr(13) & Chr(10) & _
DLookup("[cpAddress2]", "tblCompanyProfile",
"[cpCompanyID]='" & WhichDesigner & "'") & Chr(13) & Chr(10) & _
DLookup("[cpCity]", "tblCompanyProfile",
"[cpCompanyID]='" & WhichDesigner & "'") & ", " & DLookup("[cpState]",
"tblCompanyProfile", "[cpCompanyID]='" & WhichDesigner & "'") & " " &
DLookup("[cpZipCode]", "tblCompanyProfile", "[cpCompanyID]='" &
WhichDesigner & "'") & _
Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Phone
Number: " & DLookup("[cpMainPhoneNumber]", "tblCompanyProfile",
"[cpCompanyID]='" & WhichDesigner & "'") & Chr(13) & Chr(10) & _
"Fax Number: " & DLookup("[cpFaxNumber]",
"tblCompanyProfile", "[cpCompanyID]='" & WhichDesigner & "'")

End Function

 

[Stefan Hoffmann]

hi Gina,

Public Function GetDesignerAddress(ProjectID As Long) As String

Dim WhichDesigner As String
WhichDesigner = DLookup("pWhichDivision", "tblProjects", "pID =
ProjectID")

You must build a correct criteria string:

WhichDesigner = DLookup("pWhichDivision", _
"tblProjects", _
"pID = " & ProjectID)

End Function

mfG
--> stefan <--

 

[Gina Whipp]

Nevermind, I got it! More coffee needed