Unanswered: changing a part of Access query in VBA before execution
I have 8 CROSS-TAB QUERIES showing different types of loans/deposits, based on data from different tables.
They show BALANCE in VALUE FIELD for some product categories.
Now I would like to see also NUMBER OF ACCOUNTS in VALUE FIELD for these categories (I dont need to see both BALANCE and NUMBER OF ACCOUNTS at one time),
and was wondering if there is some other way to do it than having additional 8 queries with differing Value field.
Some solution so that the CROSS-TAB QUERY displays BALANCE in Value field at one time, and NUMBER OF ACCOUNTS at other time, according to what I need at the moment.
May be I could get a text of a query to VBA, and then replace the VALUE FIELD of a CROSS-TAB QUERY according to what I want at the moment - BALANCE or NUMBER OF ACCOUNTS, and after that execute the adjusted query with DoCmd.RunSQL
First of all, I dont know which functions returns the text of some query in VBA (I dont want to write the text in VBA directly, because the query changes from time to time, it would be easier just to get its text every time)
Still possible there is some completely other way to do it.
perhaps somebody was dealing with this kind of problem.
Easiest method is using DAO. Check out the QueryDef object in Help. You can read and write a querydefs SQL using the .sql property.
Beware - I once got myself in a pickle programatically screwing around with the SQL of stored queries (I basically went back to a query a year later, forgetting that it was constantly being changed by code and was convinced I had a corrupt app that would roll back my changes). I would try to not be too clever and make your change as simple as it can be. Put something in the query description indicating what you are doing too - if not for you then the next person who tries to maintain it.