To Syntax Gurus - 2 (In (Select...From...Where...)) :)

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi Guys,
May I bother you again?
Here is the expression I have some difficulties with:

stLinkCriteria = "Service PlaceID IN (SELECT
ServicePlaceID FROM tbl_ServiceAddresses WHERE Service
City = " & Chr (39) & Me!ByPlace & Chr (39) & ")"

It says:Run-time error 3075:
Syntax error in query expression.
What is the problem in here? I rocked my mind about it
too..

Thanks in advance,
John
 
John said:
Hi Guys,
May I bother you again?
Here is the expression I have some difficulties with:

stLinkCriteria = "Service PlaceID IN (SELECT
ServicePlaceID FROM tbl_ServiceAddresses WHERE Service
City = " & Chr (39) & Me!ByPlace & Chr (39) & ")"

It says:Run-time error 3075:
Syntax error in query expression.
What is the problem in here? I rocked my mind about it
too..

Thanks in advance,
John
Click to expand...

If field names contain embedded spaces or other special characters, they
must be enclosed in square brackets ([]). Assuming your field names
"Service PlaceID" and "Service City" are properly typed above, then
you'd need to do it like this:

stLinkCriteria = _
"[Service PlaceID] IN (SELECT ServicePlaceID " & _
"FROM tbl_ServiceAddresses " & _
"WHERE [Service City] = " & _
Chr (39) & Me!ByPlace & Chr (39) & ")"

Although actually you could replace the Chr(39) with the literal '
character:

stLinkCriteria = _
"[Service PlaceID] IN (SELECT ServicePlaceID " & _
"FROM tbl_ServiceAddresses " & _
"WHERE [Service City] = '" & Me!ByPlace & "')"
 
In one place, you've got Service PlaceID (with a space), in the other,
you've got ServicePlaceID (without a space). Which is it? If the space is
correct, you need to put square brackets around it:

[Service PlaceID]
 
Back
Top