Concatenate in a query not working.....syntax

  • Thread starter Thread starter himmelrich
  • Start date Start date
H

himmelrich

PM: Concatenate("Select [resource name] FROM [Project Resources] WHERE
prodid =" & [prodid] And [resource role]="PM")

the -And [resource role]="PM"- portion is not working.

Can anyone help?
 
Try:

PM: Concatenate("Select [resource name] FROM [Project Resources] WHERE
prodid =" & [prodid] & " And [resource role]='PM'")

Note that the final '" is a single ' followed by a ".

This assumes that [prodid] is an number passed into the string. If it is
text then:

PM: Concatenate("Select [resource name] FROM [Project Resources] WHERE
prodid ='" & [prodid] & "' And [resource role]='PM'")

Note that the '" & [prodid] & "' expended for clarity reads ' " & [prodid]
& " '

Good luck.

BW
 
I'm a little confused by your use of Concatenate(). AFAIK, neither Access or
JetSQL have such a function. Although Excel has a Concatenate function, it
doesn't work the way you suggest (the arguments you want combined are
separated by commas). I am therefore ignoring the Concatenate() aspect of
your post & will simply address how the string should be constructed so Jet
SQL would recognize it:
"Select [resource name] FROM [Project Resources] WHERE prodid =" & [prodid]
& " And [resource role]='PM' "


Note: the space between final ' and " is only there for the clarity of this
post. Remove it.

HTH,
 
George,

FYI
I use a generic Duane Hookom (Access MVP) has a Concatenate() function that
is often recommended for use in this situation. The poster's example lead
me (and probably others) to believe that the poster was using that function.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

George Nicholson said:
I'm a little confused by your use of Concatenate(). AFAIK, neither Access
or JetSQL have such a function. Although Excel has a Concatenate function,
it doesn't work the way you suggest (the arguments you want combined are
separated by commas). I am therefore ignoring the Concatenate() aspect of
your post & will simply address how the string should be constructed so
Jet SQL would recognize it:
"Select [resource name] FROM [Project Resources] WHERE prodid =" &
[prodid] & " And [resource role]='PM' "


Note: the space between final ' and " is only there for the clarity of
this post. Remove it.

HTH,

PM: Concatenate("Select [resource name] FROM [Project Resources] WHERE
prodid =" & [prodid] And [resource role]="PM")

the -And [resource role]="PM"- portion is not working.

Can anyone help?
Click to expand...
Click to expand...
 
Back
Top