Invalid SQL Statement - concatenation

[Guest]

My concatenation code is returning:
"Invalid SQL statement. Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.

Can anyone see the problem in my code?

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [tblProfiles.txtProfileID] &
[tblFGAttributes.Status]="Drum" & """ ORDER BY ProfilesAssociations")

Thanks!

 

[John Spencer]

I suspect that
[tblFGAttributes.Status]="Drum"
is the cause of your problem.

What are you trying to accomplish with that phrase? Are you attempting to
limit the return to only Associations that have the value "Drum" in
tblFgAttributes. If so you would need to include that table in your query
string.

You might end up with something that look like the following if my guess is
correct.

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations INNER JOIN tblFGAttributes
ON tblPKProfilesAssociations.txtProfileID= tblFGAttributes.txtProfileID
WHERE tblPKProfilesAssociations.txtProfileID =""" &
[tblProfiles.txtProfileID] &
""" and [tblFGAttributes.Status]=""Drum"" "ORDER BY ProfilesAssociations")

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

 

[Guest]

Thanks, John.

Are you attempting to limit the return to only Associations that have the value "Drum" in tblFgAttributes. If so you would need to include that table in your query string.

That's correct. Here is the SQL that works up to the point of filtering
"Drum":

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums
FROM tblProfiles
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)));

The code you listed returned errors therefore I tried my hand in adding
tblFGAttributes:

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAssociations") AS Drums, tblFGAttributes.Status
FROM tblProfiles INNER JOIN tblFGAttributes ON tblProfiles.txtProfileID =
tblFGAttributes.txtProfileID
WHERE (((tblProfiles.txtProfileID) In (SELECT txtProfileID FROM
tblPKProfilesAssociations)) AND ((tblFGAttributes.Status)="Drum"));

This returns:
The specified field '[txtProfileID]' could refer to more than one table
listed in the FROM clause of your SQL statement.

I can't see where that's the problem. Can you?

 

[Guest]

I found the problem in your code. The quotation before ORDER needed removed.
I gave it another try but the return isn't as expected.

The alien [Drums] returns but it filters for "Drum" unexpectedly. I had to
tinker a bit more and finally got it:

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations INNER JOIN tblFGAttributes
ON tblPKProfilesAssociations.ProfilesAssociations=
tblFGAttributes.txtProfileID
WHERE tblPKProfilesAssociations.txtProfileID =""" &
[tblProfiles.txtProfileID] & """ and [tblFGAttributes.Status]=""Drum"" ORDER
BY ProfilesAssociations")

Thanks so much for your help!!!

--
www.Marzetti.com

I suspect that
[tblFGAttributes.Status]="Drum"
is the cause of your problem.

What are you trying to accomplish with that phrase? Are you attempting to
limit the return to only Associations that have the value "Drum" in
tblFgAttributes. If so you would need to include that table in your query
string.

You might end up with something that look like the following if my guess is
correct.

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations INNER JOIN tblFGAttributes
ON tblPKProfilesAssociations.txtProfileID= tblFGAttributes.txtProfileID
WHERE tblPKProfilesAssociations.txtProfileID =""" &
[tblProfiles.txtProfileID] &
""" and [tblFGAttributes.Status]=""Drum"" "ORDER BY ProfilesAssociations")

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

My concatenation code is returning:
"Invalid SQL statement. Expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT',
or 'UPDATE'.

Can anyone see the problem in my code?

Drums: Concatenate("SELECT ProfilesAssociations
FROM tblPKProfilesAssociations
WHERE txtProfileID =""" & [tblProfiles.txtProfileID] &
[tblFGAttributes.Status]="Drum" & """ ORDER BY ProfilesAssociations")

Thanks!