Modify Select Query to run in module

[abesimpson]

I have the following query as SQL written by the Access Query design view. I need to modify this to extract different data MANY time, i.e., Good new 11h, 12h, etc.

SELECT [Good news scores 15h].Ticker, [Good news scores 15h].[Date/Time], [Good news scores 15h].Currentstate, [time settings].[minimum price gain for good news], ([close]-[close ref-1])/[close ref-1] AS pricegain, [minimum volume for good news]*[AvgVol] AS minvol, [close]*[Avgvol] AS [size], [Good news scores 15h].close, [Good news scores 15h].GNscore, [Good news scores 15h].BNscore, [Good news scores 15h].Tradingcost
FROM [Good news scores 15h] INNER JOIN [time settings] ON [Good news scores 15h].[base time] = [time settings].time
WHERE (((([Close] - [close ref-1]) / [close ref-1]) > [minimum price gain for good news]) And (([minimum volume for good news] * [Avgvol]) < [Good news scores 15h.volume]) And (([Close] * [Avgvol]) > 500000) And (([Good news scores 15h].Close) > 0.8) And (([Good news scores 15h].BNscore) > 0))
ORDER BY [Good news scores 15h].GNscore DESC;

I thought the simplest way would be to copy the code into a module and modify each section as nessesary (adding "DoCMD RunSQL" in front), then run the whole mess. I have been told that SELECT queries do not run under DoCMD.

How do I modify the code?

Many thanks in advance for the help.

a

[ben.oates]

Morning abesimpson,

You have a few options. I'll list a couple. You can keep the SQL in a query and access the data in VBA with this:


Dim qdf As QueryDef

Set qdf = Currentdb.QuerDefs("NameOfQuery")


Or if you want to keep it all in code (so it's less likely that users will delete it) then you would want to run the SQL to build a recordset. This would be my choice.


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(yourSQLstring)


Please note, this is all from memory, but it should set you in the right direction at least.

[abesimpson]

Ben,

Sorry oit took so long to say thanks for your advice. You have solve my problem (I used the 2nd varient).

abe.

[CreganTur]

I'm really glad Ben's solution worked for you!

Another way that you can run SQL code froM VBA is by the RunSQL function of DoCmd.

Dim SQL as String
SQL = "YourSQLCodeHere"
DoCmd.RunSQL SQL

[Trevor]

note: docmdRunSQL won't work if you are trying to hardcode your sql statmente and run it, docmdRunsql only works on action queries so you would either have to ,
If you wast to use a predefined query(query though the access query builder) the above would work but if you were trying to Docomdrunsql(select from tableX ) ' this wouldn't work because it's not an action query
or your could open the recordset and loop through it to pull the data out but then you would also have to formate the date how you want it to be seen.