SQL syntax problem

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have a query statement that runs off of the users input in a textbox on a
form. The input is placed in "Var1" and used in a SQL string shown below.
The Append in the string is used to combine two informational fields in the
source table, Project Name and Project Description; to help increase the hits
on the user's search input.
The query runs fine in the QBE grid; however, I am trying to move it into
VBA code and create a multiple query option form. The problem I'm running
into with this code is with the double quotes inside the query. I keep
getting a Compile Error: Expected: end of statement. I tried using Chr$()
values throughout the statement but I couldn't get that to work either. I
know this can be done, I'm just not getting the syntax correct. Any help
would be greatly appreciated.

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & " " & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & " " & [Brief Description]) Like "*" &
[forms]![frmSearch1]![Var1] & "*"))"

Thanks,
Al
 
To put double quotes inside a string, you need to double them up:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & "" "" & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & "" "" & [Brief Description]) Like ""*"" &
[forms]![frmSearch1]![Var1] & ""*""))"

You could also probably get away using single quotes:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & ' ' & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & ' ' & [Brief Description]) Like '*' &
[forms]![frmSearch1]![Var1] & '*'))"

although this will fail if the fields contain apostrophes.
 
Thank you Doug, that took away the error message; however, now it is stating
that: "A RunSQL action requires an argument consisting of an SQL statement.
In my code, I follow the "SelSQL" line with:

DoCmd.RunSQL SelSQL

and this where the new error is being generated. This is puzzling because
the SelSQL string is a SQL statement, is it not?

Thanks in advance,
Al

Douglas J. Steele said:
To put double quotes inside a string, you need to double them up:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & "" "" & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & "" "" & [Brief Description]) Like ""*"" &
[forms]![frmSearch1]![Var1] & ""*""))"

You could also probably get away using single quotes:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & ' ' & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & ' ' & [Brief Description]) Like '*' &
[forms]![frmSearch1]![Var1] & '*'))"

although this will fail if the fields contain apostrophes.



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al said:
I have a query statement that runs off of the users input in a textbox on a
form. The input is placed in "Var1" and used in a SQL string shown below.
The Append in the string is used to combine two informational fields in
the
source table, Project Name and Project Description; to help increase the
hits
on the user's search input.
The query runs fine in the QBE grid; however, I am trying to move it into
VBA code and create a multiple query option form. The problem I'm running
into with this code is with the double quotes inside the query. I keep
getting a Compile Error: Expected: end of statement. I tried using Chr$()
values throughout the statement but I couldn't get that to work either. I
know this can be done, I'm just not getting the syntax correct. Any help
would be greatly appreciated.

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & " " & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & " " & [Brief Description]) Like "*" &
[forms]![frmSearch1]![Var1] & "*"))"

Thanks,
Al
Click to expand...
Click to expand...
 
After researching this, I found out that the RunSQL command only works with
action queries and will not work with a Select query. I will have to figure
out a different way to run it???


Al said:
Thank you Doug, that took away the error message; however, now it is stating
that: "A RunSQL action requires an argument consisting of an SQL statement.
In my code, I follow the "SelSQL" line with:

DoCmd.RunSQL SelSQL

and this where the new error is being generated. This is puzzling because
the SelSQL string is a SQL statement, is it not?

Thanks in advance,
Al

Douglas J. Steele said:
To put double quotes inside a string, you need to double them up:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & "" "" & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & "" "" & [Brief Description]) Like ""*"" &
[forms]![frmSearch1]![Var1] & ""*""))"

You could also probably get away using single quotes:

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & ' ' & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & ' ' & [Brief Description]) Like '*' &
[forms]![frmSearch1]![Var1] & '*'))"

although this will fail if the fields contain apostrophes.



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al said:
I have a query statement that runs off of the users input in a textbox on a
form. The input is placed in "Var1" and used in a SQL string shown below.
The Append in the string is used to combine two informational fields in
the
source table, Project Name and Project Description; to help increase the
hits
on the user's search input.
The query runs fine in the QBE grid; however, I am trying to move it into
VBA code and create a multiple query option form. The problem I'm running
into with this code is with the double quotes inside the query. I keep
getting a Compile Error: Expected: end of statement. I tried using Chr$()
values throughout the statement but I couldn't get that to work either. I
know this can be done, I'm just not getting the syntax correct. Any help
would be greatly appreciated.

SelSQL = "SELECT AllProjects.Project, AllProjects.[Brief Description],
AllProjects.Code, [Project] & " " & [Brief Description] AS Append
FROM AllProjects
WHERE ((([Project] & " " & [Brief Description]) Like "*" &
[forms]![frmSearch1]![Var1] & "*"))"

Thanks,
Al
Click to expand...
Click to expand...
Click to expand...
 
Back
Top