Why is this not working? Please Help!

[Gina Whipp]

Hi All,

I have a module that has a DLookUp for addresses. However, since it's so
long I decided to make it go to multiple lines, a no brainer I thought! I
keep getting an error "Expected end of statement" on the second line on '
"cpAddress" '. Can someone tell me what I am missing, I think I tried every
concaction but obviously not the right one.

GetAddress = DLookup("Client", "qryClientNames", "cpClientID = " & N) &
Chr(13) & Chr(10) & _
"DLookUp("cpAddress","tblClientProfile","cpClientID = "
& N) & Chr(13) & Chr(10)


Thanks
Gina Whipp

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

 

[fredg]

Hi All,

I have a module that has a DLookUp for addresses. However, since it's so
long I decided to make it go to multiple lines, a no brainer I thought! I
keep getting an error "Expected end of statement" on the second line on '
"cpAddress" '. Can someone tell me what I am missing, I think I tried every
concaction but obviously not the right one.

GetAddress = DLookup("Client", "qryClientNames", "cpClientID = " & N) &
Chr(13) & Chr(10) & _
"DLookUp("cpAddress","tblClientProfile","cpClientID = "
& N) & Chr(13) & Chr(10)

Thanks
Gina Whipp

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

The problem is the double quote in front of the 2nd DLookUp.
Access is expecting a closing quote to match with it.

"DLookUp("cpAddress", etc...) should be
DLookUp("cpAddress", etc ...)

 

[Gina Whipp]

Thanks Fred!!!

Got that working... NOW it refuses to show me what I asked for... Any
ideas??? I know I got something wrong here. No message just nothing and
yep there's sample data in the table.

Function GetAddress(N)

Dim CA As String

CA = DLookup("Client", "qryClientNames", "cpClientID =" & N) & Chr(13) &
Chr(10) & _
DLookup("cpAddress", "tblClientProfile", "cpClientID = " &
N) & Chr(13) & Chr(10) & _
DLookup("cpAddress2", "tblClientProfile", "cpClientID = " &
N) & Chr(13) & Chr(10) & _
DLookup("[cpCityOrTown]", "tblClientProfile", "cpClientID =
" & N) & ", " & DLookup("[cpStateOrProvince]", "tblClientProfile",
"cpClientID = " & N) & " " & DLookup("[cpPostalCode]", "tblClientProfile",
"cpClientID = " & 1) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
"Phone Number: " & DLookup("[cpMainPhone]",
"tblClientProfile", "cpClientID = " & N) & _
Chr(13) & Chr(10) & "Fax Number: " & DLookup("[cpMainFax]",
"tblClientProfile", "cpClientID = " & N)

GetAddress = CA

End Function

 

[John Vinson]

Hi All,

I have a module that has a DLookUp for addresses. However, since it's so
long I decided to make it go to multiple lines, a no brainer I thought! I
keep getting an error "Expected end of statement" on the second line on '
"cpAddress" '. Can someone tell me what I am missing, I think I tried every
concaction but obviously not the right one.

GetAddress = DLookup("Client", "qryClientNames", "cpClientID = " & N) &
Chr(13) & Chr(10) & _
"DLookUp("cpAddress","tblClientProfile","cpClientID = "

You've got an extra doublequote before the DLookUp on this line.

& N) & Chr(13) & Chr(10)

John W. Vinson[MVP]

 

[fredg]

Thanks Fred!!!

Got that working... NOW it refuses to show me what I asked for... Any
ideas??? I know I got something wrong here. No message just nothing and
yep there's sample data in the table.

Function GetAddress(N)

Dim CA As String

CA = DLookup("Client", "qryClientNames", "cpClientID =" & N) & Chr(13) &
Chr(10) & _
DLookup("cpAddress", "tblClientProfile", "cpClientID = " &
N) & Chr(13) & Chr(10) & _
DLookup("cpAddress2", "tblClientProfile", "cpClientID = " &
N) & Chr(13) & Chr(10) & _
DLookup("[cpCityOrTown]", "tblClientProfile", "cpClientID =
" & N) & ", " & DLookup("[cpStateOrProvince]", "tblClientProfile",
"cpClientID = " & N) & " " & DLookup("[cpPostalCode]", "tblClientProfile",
"cpClientID = " & 1) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
"Phone Number: " & DLookup("[cpMainPhone]",
"tblClientProfile", "cpClientID = " & N) & _
Chr(13) & Chr(10) & "Fax Number: " & DLookup("[cpMainFax]",
"tblClientProfile", "cpClientID = " & N)

GetAddress = CA

End Function

Regarding this line:

DLookup("[cpPostalCode]", "tblClientProfile", "cpClientID = " & 1)<

Is this DLookUp correct? It calls for a cpClientID of 1, not N (as all
the others do).
If 1 is correct, you can simplify just that one DLookUp a bit to:
DLookup("[cpPostalCode]", "tblClientProfile", "cpClientID = 1")

Is cpClientID a number datatype or text?
If it's a number, try
Function GetAddress(N as Integer) as String

If that still doesn't return a value,then try it thuis way:

Function GetAddress(N as Integer) as String
Dim CA as String
CA= DLookup("Client", "qryClientNames", "cpClientID =" & N) & Chr(13)
& Chr(10)
MsgBox CA
CA = CA & DLookup("cpAddress", "tblClientProfile", "cpClientID = " &
N) & Chr(13) & Chr(10)
MsgBox CA
CA = CA & DLookUp( etc...)
MsgBox CA
etc.
GetAddress = CA
MsgBox GetAddress
End Function

If it fails, where does your function fail?

If cpClientID is a text datatype, then the N variable must be
enclosed in single quotes.

Function GetAddress(N as String) as String
Dim CA as String
CA= DLookup("Client", "qryClientNames", "cpClientID = '" & N & "'") &
Chr(13) & Chr(10)

....etc... for all the other DLookUps.

End Function

Note: When you use VBA, you can use either vbCrLf or VBNewLine in
place of chr(13) & chr(10). It would make it just a bit easier to
write and make it easier follow what's happening when you come back to
this in 6 months for modifications.

 

[Gina Whipp]

All fixed, forgot to put the question mark in, went and got more coffee.
Oh, and I did have an extra ) mark, fixed that too.

Big THANKS to all,
Gina Whipp

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