DoCmd.RunSQL (what am I missing?)

[JPFred]

I am attempting to run an SQL statement from code when a check box is clicked on. I build the statement (seen below) and try to execute it using the DoCmd.RunSQL function. When I execute the code I a 2342 run time error. If I use the the DoCmd.OpenQuery function passing the name of a query defined below it displays a pop-up asking me for the parameter value (which is already defined in the query). In the following example the value of strchksearch is "*AVI*".

Query definition:

SELECT [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year]
FROM main
WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE [strchksearch] ORDER BY Author);

Function definition:

Private Sub chkauthor_Click()
 Dim strFullQuery As String
 Dim strQueryPart1 As String
 Dim strQueryPart2 As String
 Dim strQueryPart3 As String
 Dim strdoublequote As String

strdoublequote = """"

 strQueryPart1 = "SELECT [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year] FROM main WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE "
 
strQueryPart2 = ("*" & txtsearch.Value & "*")

strQueryPart3 = " ORDER BY Author);"

 strFullQuery = strQueryPart1 & strdoublequote & "*" & strQueryPart2 & "*" & strdoublequote & strQueryPart3

 DoCmd.RunSQL strFullQuery

End Sub

The code using DoCmdOpenQuery is as follows:

Private Sub chkauthor_Click()
dim strsearchit as string
dim strdoublequote as string

 strdoublequote = """"
 strsearchit = ("*" & txtsearch.Value & "*")

  DoCmd.OpenQuery ("testsearch")

End Sub

 

[Uncle Gizmo]

This:-

strQueryPart2 = ("*" & txtsearch.Value & "*")

isnt a string

 

[isladogs]

I believe you need

LIKE '" & strchksearch & "'

Also your strfullquery has 2 sets of wildcards between the """"

 

[arnelgp]

still it will not work and you havr to make strsearchit variable public in a standard module.

 

[Solo712]

I am attempting to run an SQL statement from code when a check box is clicked on. I build the statement (seen below) and try to execute it using the DoCmd.RunSQL function. When I execute the code I a 2342 run time error. If I use the the DoCmd.OpenQuery function passing the name of a query defined below it displays a pop-up asking me for the parameter value (which is already defined in the query). In the following example the value of strchksearch is "*AVI*".

Query definition:

SELECT [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year]
FROM main
WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE [strchksearch] ORDER BY Author);

Function definition:

Private Sub chkauthor_Click()
 Dim strFullQuery As String
 Dim strQueryPart1 As String
 Dim strQueryPart2 As String
 Dim strQueryPart3 As String
 Dim strdoublequote As String

strdoublequote = """"

 strQueryPart1 = "[COLOR="red"]SELECT[/COLOR] [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year] FROM main WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE "
 
strQueryPart2 = ("*" & txtsearch.Value & "*")

strQueryPart3 = " ORDER BY Author);"

 strFullQuery = strQueryPart1 & strdoublequote & "*" & strQueryPart2 & "*" & strdoublequote & strQueryPart3

 [COLOR="Red"]DoCmd.RunSQL[/COLOR] strFullQuery

End Sub

You get the error on the DoCmd.RunSQL query because you can't use with a SELECT query. You should use

Currentdb.Execute strFullQuery

instead. In the second example, obviously something is missing. How and where do you enter the strchksearch argument into the SQL string?

Best,
Jiri

 

[isladogs]

Several things need attention

Solo has just beaten me to the first point ...
You can ONLY run ACTION queries - not SELECT queries

But to disagree with Solo, you can't execute them either (error 3065)

Here's a much simplified version of your query def:

SELECT [access], Author, Subject, Title, Pub, [Year]
FROM main
WHERE Author IN (SELECT [Author] FROM [main] WHERE [Author] LIKE '*' & strchkSearch & '*' ORDER BY Author);

I've specified the wildcards here and suggest you just use

strchksearch ="AVI"

to avoid duplication

You don't need to prefix the fields with main. as all fields are from the 1 table
However you are using a reserved word so [Year] is in square brackets
I don't think access is a reserved word though it ought to be ...!
Personally I'd recommend avoiding ALL reserved words

I'd also question the procedure 'Private Sub chkauthor_Click()'
First of all it's a sub not a function & I assume its for a checkbox on the same form.
However you've given 2 totally different code lists for it - very confusing

 

[Uncle Gizmo]

This:-

strQueryPart2 = ("*" & txtsearch.Value & "*")

isnt a string

Ah! I see it is a string, it's just contained as a mathematical expression! Haven't seen that before, can't quite work out why it's necessary?

 

[Solo712]

Several things need attention

Solo has just beaten me to the first point ...
You can ONLY run ACTION queries - not SELECT queries

But to disagree with Solo, you can't execute them either (error 3065)

Here's a much simplified version of your query def:

SELECT [access], Author, Subject, Title, Pub, [Year]
FROM main
WHERE Author IN (SELECT [Author] FROM [main] WHERE [Author] LIKE '*' & strchkSearch & '*' ORDER BY Author);

I've specified the wildcards here and suggest you just use
to avoid duplication

You don't need to prefix the fields with main. as all fields are from the 1 table
However you are using a reserved word so [Year] is in square brackets
I don't think access is a reserved word though it ought to be ...!
Personally I'd recommend avoiding ALL reserved words

I'd also question the procedure 'Private Sub chkauthor_Click()'
First of all it's a sub not a function & I assume its for a checkbox on the same form.
However you've given 2 totally different code lists for it - very confusing

My bad on the Execute, Colin... a brain fart , of course OpenRecordset should be used to get a result of SELECT query.

Best,
Jiri

 

[JPFred]

Yes I did supply 2 sets of code in an attempt to show the two solutions I have tried. I still am confused about how to execute my query/sql passing the value of txtsearch to the query/sql.

 

[JPFred]

I am using the code:

strQueryPart2 = ("*" & txtsearch.Value & "*")

to build an sql statement that will look like *datavaulehere*.
By dueing this I was able to build my sql that will find datavaluehere anywhere within a db field.

 

[isladogs]

Look at my answer in post #6

 

[MarkK]

This is not how you use square brackets in SQL text...

SELECT [table.field1], [table.field2]
FROM table

...it should be...

SELECT [table].[field1], [table].[field2]
FROM table

hth
Mark