DoCmd.RunSQL (what am I missing?) (1 Viewer)

JPFred · Nov 19, 2017

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:

Code:

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:

Code:

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:

Code:

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

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

  DoCmd.OpenQuery ("testsearch")

End Sub

Uncle Gizmo · Nov 19, 2017

This:-

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

isnt a string

isladogs · Nov 19, 2017

I believe you need

Code:

LIKE '" & strchksearch & "'

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

arnelgp · Nov 19, 2017

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

Solo712 · Nov 19, 2017

JPFred said:
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:

Code:

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:

Code:

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

Code:

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 · Nov 19, 2017

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:

Code:

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 · Nov 19, 2017

Uncle Gizmo said:
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 · Nov 19, 2017

ridders said:
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:

Code:

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 · Nov 20, 2017

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 · Nov 20, 2017

I am using the code:

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 · Nov 20, 2017

Look at my answer in post #6

MarkK · Nov 20, 2017

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

Code:

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

...it should be...

Code:

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

hth
Mark

DoCmd.RunSQL (what am I missing?) (1 Viewer)

JPFred

Registered User.

Uncle Gizmo

Nifty Access Guy

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

Solo712

Registered User.

isladogs

MVP / VIP

Attachments

Uncle Gizmo

Nifty Access Guy

Solo712

Registered User.

JPFred

Registered User.

JPFred

Registered User.

isladogs

MVP / VIP

MarkK

bit cruncher

Similar threads

Users who are viewing this thread