Syntax Error in Union Query

[Gina Whipp]

I get Syntax error (missing operator) in query expression 'vpVendorID='.

Please explain, as both are Long Integers, what I am doing wrong.

SELECT IIf([cpContactTypeID]=2, DLookUp("vpCompanyName","tblVendorProfile",
"vpVendorID=" & cpContactID), cpDepartment) AS DisplayName, cpFirstName,
cpLastName, cpOfficeNumber, cpContactTypeID
FROM tblContactProfile
UNION ALL SELECT vpCompanyName, Null, Null, vpMainPhone, "V"
FROM tblVendorProfile
UNION ALL SELECT cpCompanyName,cpFirstName, cpLastName, cpMainPhone, "C"
FROM tblCustomerProfile;

Thanks in advance!
Gina

 

[Gina Whipp]

Neverming, I got it...

 

[John Vinson]

I get Syntax error (missing operator) in query expression 'vpVendorID='.

Please explain, as both are Long Integers, what I am doing wrong.

SELECT IIf([cpContactTypeID]=2, DLookUp("vpCompanyName","tblVendorProfile",
"vpVendorID=" & cpContactID), cpDepartment) AS DisplayName, cpFirstName,
cpLastName, cpOfficeNumber, cpContactTypeID
FROM tblContactProfile
UNION ALL SELECT vpCompanyName, Null, Null, vpMainPhone, "V"
FROM tblVendorProfile
UNION ALL SELECT cpCompanyName,cpFirstName, cpLastName, cpMainPhone, "C"
FROM tblCustomerProfile;

Thanks in advance!
Gina

It would appear that there are records in tblContactProfile for which
cbContactTypeID is equal to 2, but for which cbContactID is NULL. It's
generating an DLookUp call

DLookUp("vpCompanyName","tblVendorProfile", "vpVendorID=")

since there's no ID to fill in. What do you want to look up in this
case?

John W. Vinson[MVP]

 

[Gina Whipp]

John, that is exactly what it was...

I get Syntax error (missing operator) in query expression 'vpVendorID='.

Please explain, as both are Long Integers, what I am doing wrong.

SELECT IIf([cpContactTypeID]=2,
DLookUp("vpCompanyName","tblVendorProfile",
"vpVendorID=" & cpContactID), cpDepartment) AS DisplayName, cpFirstName,
cpLastName, cpOfficeNumber, cpContactTypeID
FROM tblContactProfile
UNION ALL SELECT vpCompanyName, Null, Null, vpMainPhone, "V"
FROM tblVendorProfile
UNION ALL SELECT cpCompanyName,cpFirstName, cpLastName, cpMainPhone, "C"
FROM tblCustomerProfile;

Thanks in advance!
Gina

It would appear that there are records in tblContactProfile for which
cbContactTypeID is equal to 2, but for which cbContactID is NULL. It's
generating an DLookUp call

DLookUp("vpCompanyName","tblVendorProfile", "vpVendorID=")

since there's no ID to fill in. What do you want to look up in this
case?

John W. Vinson[MVP]