Removing Fields From Query using form

[errolf]

I have a query that gets its criteria from a form. Is it possible to remove or replace fields in the query from the form in order to increase or decrease the data required.
TRANSFORM Sum(round([NETT]-[gst])) AS Sales
SELECT SCEX.AC_NO, DREX.NAME, DREX.CLASS, retgrp([SCEX].[CLASS]) AS GRP, [Forms]![StatsSboard]![Classif] AS Sel, CarrTerrTbl.NAME,
E.G. Add or remove SCEX.DESC or any other field for that matter.
The end result being sales by customer by product by supplier or sales by customer by product by terr or any other drill down combination required without having separate queries

In anticipation

Errolf

 

[scriverb]

You can write your entire query from code if you want to. The SQL behind a query is known as the .SQL property of the query collection. So the following is an example of how to do what you want:
Dim db as DAO.database
db.QueryDefs("qryTempQuery&quot.SQL = "TRANSFORM " & _
"Sum(round([NETT]-[gst])) AS Sales " & _
"SELECT SCEX.AC_NO, DREX.NAME, " & _
"DREX.CLASS, retgrp([SCEX].[CLASS]) AS GRP, " & _
[Forms]![StatsSboard]![Classif] & " AS Sel, " & _
"CarrTerrTbl.NAME,. . .

You can use IIF function statments to analyze a form to determine which fields to add to the Select. Basicially you are just building a big string which when finished is assigned as the .SQL property of the target query named "qryTempQuery". This name could be any named query in the query collection. I just use a generic name for this query and keep updating it with new SQL.

In the example above I used the & _ to cancatonate and wraparound to the next line. Didn't want to confuse you with that. The ampersand cancatenates the individual strings together and the underscore tells ACCESS that the statement continues on the next line. It is also easier for reaching and making sure of the spaces of the final product.

I hope that this helps you in your project. Bob Scriver

 

[errolf]

Thanks Bob for your reply I tried out yur suggestion but I must be missing something. I used the following code to try it out but got an error message "Compile Error User-Defined type not defined" (This part highlighted 'db As DAO.database'

Private Sub CmdSelect_Click()
Dim db As DAO.database
db.QueryDefs("qryCBGSCOTT&quot.Sql = "TRANSFORM " & _
"Sum(round([NETT]-[gst])) AS Sales " & _
"SELECT SCEX.AC_NO, DREX.NAME, " & _
"DREX.CLASS, retgrp([SCEX].[CLASS]) AS GRP, " & _
[Forms]![StatsSboard]![Classif] & " AS Sel, " & _
"CarrTerrTbl.NAME"

End Sub
Many thanks
Errol Featherstone

 

[scriverb]

Sounds like a Library reference to the DAO Object Library is missing. Check your Library references and make sure that you have the Microsoft DAO x.xx Object Library selected. Bob Scriver

 

[errolf]

Hi Bob thanks for your prompt reply and perseverance,
I selected the DAO library as per your instruction I now get another message "Object variable or With block variable not set"
Run-time error 91
Thanks Again
Errol

 

[scriverb]

Sorry for a line of code:

Dim db as DAO.database
Set db = CurrentDB
db.QueryDefs("qryTempQuery&quot.SQL = "TRANSFORM " & _
". . . . .

Insert the red line of code and give it another try.
Bob Scriver

 

[errolf]

Thanks Bob, I now have got the principal to work and can now finish the project.
Thank you very much for you help

Errol