Query Criteria and excluding fields when extracting the query

[NickWren]

So I have a query that a user wants to be able to pick and choose the fields they want to extract to excel.


Ex. Lets say the first time the user wants to extract fields A, B, and C and next time they want A, B, D, and E and Next time A,E,F,G and H. (So they might have a similar fields as last time but the user wants to define the fields they want to extract)

I was thinking about making a checkbox for each field in the query and if the checkbox was checked then extract the values for that field, is there an easier way of doing this?

Next lets use the example from above where they want to extract A, E, F, G, and H.
Now for A's criteria they want everyone with their name starting with J
For E's criteria they want a date later than 2017
For F's criteria they want anyone over the age of 30
for G's they want someone with a score higher than 80
and for H's criteria they want someone with a certain diagnosis.

I know how criteria works;
Ex. Like "J*" for A's Criteria, Year(ReceivedDate) >= 2017, Age > 30, Score > 80, "Some Diagnosis"

But now putting this all together in one line or each on their own separate criteria line the criteria doesnt work properly for me; either not filtering anything or not being able to find any samples that fall into that criteria even though I know some samples have that as criteria. ( I am going to need this to work with the above question)

Side note: How would you extract what you see on a (data table) subform?

[June7]

This will require VBA that modifies query object referencing QueryDefs. Here is example code that just modifies query filter criteria:

Private Sub btnRun_Click()
If IsNull(Me.tbxFilter) Then
   Me.tbxFilter = "SELECT * FROM ProjectRatesMainSub WHERE grading Like '*' " & GetMisc() & " ORDER BY projects.proj_num, grading;"
End If
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery"


Private Sub btnExcel_Click()
Dim qdfUser As DAO.QueryDef
CurrentDb.QueryDefs.Delete ("UserQuery")
Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
DoCmd.OpenQuery "UserQuery", , acReadOnly
DoCmd.RunCommand acCmdExportExcel
End Sub

Here is the code for the GetMisc() function that builds the filter criteria:

Public Function GetMisc() As String
GetMisc = GetMisc & IIf(Me.chkIssue, " major_issue=True AND", "")
GetMisc = GetMisc & IIf(Me.chkMaterial, " new_material=True AND", "")
GetMisc = GetMisc & IIf(Me.chkTechnique, " new_technique=True AND", "")
GetMisc = GetMisc & IIf(Me.chkSpec, " new_spec=True AND", "")
If GetMisc <> "" Then GetMisc = " AND " & Left(GetMisc, Len(GetMisc) - 4) & " "
End Function

You will need more code that dynamically builds the field string to replace the * as shown in my SQL statement.