Run SQL Statement in Access using VBA code

Question

I can't figure out what is wrong in this, I'm collecting search criteria from a Form to use it in search.

The SQL line stored like this : (strWhere is the collected info from the Form)

SQLst = "SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where " & strWhere & ";"

The final SQL statement looks like this:

SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where ([Account Number] = "1234");

Now After I run the Line using this command (SQLst is the SQL Line up)

DoCmd.OpenQuery SQLst

I get this message:

Solution

Not DoCmd.OpenQuery SQLst, that is for saved queries, not SQL strings. You need:

' Best to use a separate instance, so you can get record counts etc.
Set db = CurrentDB

For action queries:

db.Execute SQLst, dbFailOnerror

For SELECT queries, you can either use a recordset or update or create a query.

Set rs = db.Openrecordset(SQLst)

' This query does not exist
Set qdf = db.CreateQueryDef("MyQuery", SQLst)

I doubt that the account number is text, so:

 ([Account Number] = 1234);

Quotes are used for text-type fields / columns, numbers are as is and dates are delimited with hash (#).

Comments

[Gord Thompson]

cc: @user1641782 ...and, as is so often repeated here and elsewhere, using a parameterized query is always better than "SQL glue-up".

[Mohamed Fleifel]

thanks, for the reply, i tried it but it gived me an error, when i searched for it, found this office.microsoft.com/en-us/access-help/HV080760050.aspx

[Mohamed Fleifel]

It seems that there is no way i can run the select with execute command.your comment about the quotes was useful it did made some problem while testing. and also thanks for Gord.