Changing SQL of the Query on the 'fly'

[mcdhappy80]

Can I change the SQL of the query, which is record source, with VBA?

Thank You

 

[SOS]

Yes, you can.

Do a search here on QueryDef and you might get some. If you can't find enough, post back.

 

[SimonB1978]

Hi,

CurrentDB.QueryDefs("your_query_name").SQL = new_sql
Forms("form_name").Requery (or Me.Requery if you are in the form)

That should do it.

Simon B.

 

[mcdhappy80]

Thank You guys, will try it

 

[Galaxiom]

For simple queries I tend to write them directly into the Record Source itself. To change the text of this query use this VBA.

Me.Form.RecordSource = "SELECT .... etc "

This doesn't clutter up the navigation bar with extra stored queries and is a good idea if the the query is not used multiple times in the design. Obviously it is easier to enter a name in each form than the full sql.

However I think it does mean that Access has to construct the execution plan every time it runs the query so it is best to use a stored query for complex selections.

In this case I would also avoid changing the sql of the querydef for the same reason. Access has to make a new plan when you change the sql.

Better to change to another stored query by changing the name in the RecordSource property. This way the plan is not lost.

 

[mcdhappy80]

The reason I need to change the query SQL is because:
I have report which source is table. When I create filter in the reports on load event the data is not displayed as I want it to.
The query with the same filter as report extracts data properly.
When I say filter I mainly mean in the WHERE clause of the query SQL and the filter in on load event which I create with VBA.
Then I tried to change the record source of the report from table to that query to see will it display the data and it does. So now I'm thinking if the filter doesn't display right and the query does I'll put the query as record source. From my experience these filters are behaving unpredictably sometimes and I'm searching different approach now.
Thank You once again

======================================

I tried to change query definition, and I have the same problem with that approach too.
Why does report display set of data which I don't want (besides those I want and have in query) and query doesn't?
To explain better, the query with the same query definition, which is not created with VBA but in query editor, displays the wanted data correctly, and the one I create on the fly, doesn't, why is that?

 

[SimonB1978]

Hi,

I suggest you create a string of your query before assigning it to the SQL property of the querydef. You can then print it out to make sure the syntax is ok (spacing, quotation mark, etc, ...).

Ex:

strSQL = "SELECT ....."
Debug.Print strSQL
CurrentDB.QueryDefs(query_name).SQL = strSQL ' or Me.RecordSource = strSQL

HTH

Simon B.

 

[vbaInet]

I think the OP just wants to filter the record source of the report using the WHERE clause.

This can be achieved using the WHERE argument of the Docmd.Openreport method. Have a look at these:

http://www.mvps.org/access/reports/rpt0002.htm
http://baldyweb.com/wherecondition.htm

 

[mcdhappy80]

Thank You guys for Your advices, but I found out what was the problem.
Apparently the Access makes difference when You create SQL in queries with and without ")" sign, which is put there by default when You use query builder.
The query that worked before my problem had ")" because it was constructed with query builder. The SQL i tried to create on the fly didn't have ")" because I don't like them there (they're clogging things up for me).
Now I had to put the ")" and everything works.

I would really want to know why is this the way it is, what is the difference if You put or don't put ")" sign in Your SQL?
I have to say I studied SQL from many online sources and the only place the ")" shows up is in Access.

Thank You

 

[SOS]

Are you sure you don't mean ;

 

[mcdhappy80]

[SOLVED] Changing SQL of the Query on the 'fly'

Are you sure you don't mean ;

No because this query ... :

SELECT tblZaduzivanje.txtTipNaplateID, tblZaduzivanje.txtMestoPohID, tblZaduzivanje.dblIznosZaNaplatu, tblZaduzivanje.dteDatumZaduzivanja
FROM tblZaduzivanje
WHERE (((tblZaduzivanje.txtTipNaplateID)<>'R00' And (tblZaduzivanje.txtTipNaplateID)<>'R01' And (tblZaduzivanje.txtTipNaplateID)<>'R02' And (tblZaduzivanje.txtTipNaplateID)<>'R02' And (tblZaduzivanje.txtTipNaplateID)<>'R03' And (tblZaduzivanje.txtTipNaplateID)<>'R04' And (tblZaduzivanje.txtTipNaplateID)<>'R05' And (tblZaduzivanje.txtTipNaplateID)<>'R06' And (tblZaduzivanje.txtTipNaplateID)<>'R07' And (tblZaduzivanje.txtTipNaplateID)<>'S00' And (tblZaduzivanje.txtTipNaplateID)<>'S01' And (tblZaduzivanje.txtTipNaplateID)<>'S02' And (tblZaduzivanje.txtTipNaplateID)<>'S02' And (tblZaduzivanje.txtTipNaplateID)<>'S03' And (tblZaduzivanje.txtTipNaplateID)<>'S04' And (tblZaduzivanje.txtTipNaplateID)<>'S05' And (tblZaduzivanje.txtTipNaplateID)<>'S06' And (tblZaduzivanje.txtTipNaplateID)<>'S07') AND ((tblZaduzivanje.txtMestoPohID)='801' Or (tblZaduzivanje.txtMestoPohID)='802'));

...,and this query:

SELECT tblZaduzivanje.txtTipNaplateID, tblZaduzivanje.txtMestoPohID, tblZaduzivanje.dblIznosZaNaplatu, tblZaduzivanje.dteDatumZaduzivanja
FROM tblZaduzivanje
WHERE tblZaduzivanje.txtTipNaplateID<>'R00' And tblZaduzivanje.txtTipNaplateID>'R01' And tblZaduzivanje.txtTipNaplateID<>'R02' And tblZaduzivanje.txtTipNaplateID<>'R02' And tblZaduzivanje.txtTipNaplateID<>'R03' And tblZaduzivanje.txtTipNaplateID<>'R04' And tblZaduzivanje.txtTipNaplateID<>'R05' And tblZaduzivanje.txtTipNaplateID<>'R06' And tblZaduzivanje.txtTipNaplateID<>'R07' And tblZaduzivanje.txtTipNaplateID<>'S00' And tblZaduzivanje.txtTipNaplateID<>'S01' And tblZaduzivanje.txtTipNaplateID<>'S02' And tblZaduzivanje.txtTipNaplateID<>'S02' And tblZaduzivanje.txtTipNaplateID)<>'S03' And tblZaduzivanje.txtTipNaplateID<>'S04' And tblZaduzivanje.txtTipNaplateID<>'S05' And tblZaduzivanje.txtTipNaplateID<>'S06' And tblZaduzivanje.txtTipNaplateID<>'S07' AND tblZaduzivanje.txtMestoPohID='801' Or tblZaduzivanje.txtMestoPohID='802';

... don't return the same result.

The second query returns R00 - R07 results for the 802 group.
It returns correct results for the 801 group, but for 802 those (R00 - R07) I don't want.

 

[vbaInet]

All you should be doing is writing the sql in code and setting the recordsource directly. Galaxiom also advised that.

 

[mcdhappy80]

[SOLVED] Changing SQL of the Query on the 'fly'

All you should be doing is writing the sql in code and setting the recordsource directly. Galaxiom also advised that.

Yes, I tried that, it works just as filtering the report with VBA, but the problem was with parenthesis in SQL.
I just wanted to say that for future reference if someone has the same problem as I did ...

Thank You

 

[SOS]

Re: [SOLVED] Changing SQL of the Query on the 'fly'

Yes, I tried that, it works just as filtering the report with VBA, but the problem was with parenthesis in SQL.

Actually the problem was not simply the parentheses. It is the OR with the ANDS that is the problem and the parentheses ensure the correct order of operations.

 

[vbaInet]

Re: [SOLVED] Changing SQL of the Query on the 'fly'

Yes, I tried that, it works just as filtering the report with VBA, but the problem was with parenthesis in SQL.
I just wanted to say that for future reference if someone has the same problem as I did ...

Thank You

From what SOS said, you can handle these using IF ... ELSE statement or IIF() function. There are some examples of how to do this on the forum. Once you overcome this, you will find this approach very useful.

 

[mcdhappy80]

Could You post links to posts where the IFF examples can be seen?
Thank You

 

[vbaInet]

I don't know off by hear where these examples are but if you use the search tool on this forum, you should find some appropriate threads.

An example is:

" tblZaduzivanje.txtMestoPohID='801' " & IIF(IsCombined = Yes, "OR", "AND") & " tblZaduzivanje.txtMestoPohID='802';

Where IsCombined could be a checkbox or a variable that indicates whether you want an AND or a OR operator used in the WHERE close. It just gives you an idea of how it could be used. Remember that all an IIF() function does is return one of the two parts in its arguments so that can be easily embedded in your sql string.

 

[mcdhappy80]

I don't know off by hear where these examples are but if you use the search tool on this forum, you should find some appropriate threads.

An example is:

" tblZaduzivanje.txtMestoPohID='801' " & IIF(IsCombined = Yes, "OR", "AND") & " tblZaduzivanje.txtMestoPohID='802';

Where IsCombined could be a checkbox or a variable that indicates whether you want an AND or a OR operator used in the WHERE close. It just gives you an idea of how it could be used. Remember that all an IIF() function does is return one of the two parts in its arguments so that can be easily embedded in your sql string.

Thnx, now I see what You had in mind.
Thank You

 

[vbaInet]

Glad to have had some input.