Parameters in query expressions

[dgmdvm]

I have a cross tab query that has an expression in a value field. The expression includes a Dcount calculation from a different query. The main query returns records based on a range of dates so there are date parameters in the main query. The entire cross tab query works correctly when I run it. What I would like to do however, is filter the results in the cross tab query AND in the query that lies inside the expression based on a parameter that will limit the results to either persons that are senior citizens or persons that are veterans. In the cross tab query, I have added row headings for senior citizens and veterans. I have VBA code in a procedure that calls the report that will filter on one of these two parameters. What I don't know how to do is to put the same filter on the query that is inside the expression of the cross tab query. My goal here is to use the same report container, and the same query container for both sets of reports. I know that I could set up separate queries for all of this but I was trying to be economical with my code as well as my queries. Is this even possible?



The following is the procedure that calls the report. It has multiple case statements but I removed most of them since they don't apply to my question. Case 9 returns a report of all individuals. Case 11 and Case 12 return reports that are filtered to return only veterans or senior citizens. The same report is used for all 3 Case statements. The On load event of the report declares which query is used to populate the report. That On load event is not listed here as it works correctly.

Private Sub CmdReport_Click()
  On Error GoTo Err_CmdReport_Click
    
  Dim startdate As Date
  Dim enddate As Date
  Dim DateRange As String
  Dim rptlabel As String
  
  If GetPrinter() = False Then
         Msgbox " Printers must be set up in the print settings menu before printing reports.", vbOKOnly, "Need Printer setup"
         Exit Sub
  ElseIf IsNull(Me.Text2.Value) Then
         Msgbox " Please enter the range of dates for the report", vbOKOnly, "Missing dates"
         Me.Text2.SetFocus
         Exit Sub
  ElseIf IsNull(Me.Text4.Value) Then
         Msgbox " Please enter the range of dates for the report", vbOKOnly, "Missing dates"
         Me.Text4.SetFocus
         Exit Sub
  
    
  End If
     If Frame89.Value = 7 Then
         DoCmd.OpenForm "Client_List_Reports", acNormal, , , acFormEdit, acWindowNormal, DateRange
     End If
  startdate = Me.Text2.Value
  enddate = Me.Text4.Value
  DateRange = "tblServiceInstance.Indate >= #" & startdate & "# AND tblServiceInstance.Indate <= #" & enddate & "#"
 
 
     
     Select Case Me.Frame89.Value


     Case 9
     rptname = "IndividualsAssistedbyCounty_Report"
     prnttype = "Document"
     qryname = "qryIndividualsAssisted_Crosstab"
     rptlabel = "Individuals"
     If RecordCount(qryname) = 0 Then
        Msgbox "There was no activity for this range of dates", vbOKOnly
        Exit Sub
     End If

     DoCmd.OpenReport rptname, acViewReport, , , acWindowNormal, rptlabel
     Set rpt = Reports(rptname)
     Set rpt.Printer = Application.Printers(Selprnt(prnttype))
     rpt.Printer.Orientation = acPRORPortrait
    
     
     Case 11
     rptname = "IndividualsAssistedbyCounty_Report"
     prnttype = "Document"
     qryname = "qryIndividualsSpecialAssisted_Crosstab"
      rptlabel = "Veteran"
     If RecordCount(qryname) = 0 Then
        Msgbox "There was no activity for this range of dates", vbOKOnly
        Exit Sub
     End If

     DoCmd.OpenReport rptname, acViewReport, , "Cl_Veteran = True ", acWindowNormal, rptlabel
     Set rpt = Reports(rptname)
     Set rpt.Printer = Application.Printers(Selprnt(prnttype))
     rpt.Printer.Orientation = acPRORPortrait
     

     Case 12
     rptname = "IndividualsAssistedbyCounty_Report"
     prnttype = "Document"
     qryname = "qryIndividualsSpecialAssisted_Crosstab"
     rptlabel = "Senior Citizen"
     If RecordCount(qryname) = 0 Then
        Msgbox "There was no activity for this range of dates", vbOKOnly
        Exit Sub
     End If

     DoCmd.OpenReport rptname, acViewReport, , "Cl_ageType= 'Senior Citizen' ", acWindowNormal, rptlabel
     Set rpt = Reports(rptname)
     Set rpt.Printer = Application.Printers(Selprnt(prnttype))
     rpt.Printer.Orientation = acPRORPortrait
    
     
    
Case Else    'no option buttons have been selected

       Msgbox "You must select at least one option to generate a report"

    End Select

    
Set rpt = Nothing
Application.Printer = Nothing

Exit_Err_CmdReport_Click:
  Exit Sub

Here is the edit view of the query used to filter by senior citizens only. In the expression field, I have spelled out that the underlying query "qryFurnitureOnHoldSuummary" filter on the [CL_agetype] field. That I am trying to do is replace that explicit criteria with a parameter that I can declare in the VBA code. I highlighted the criteria that I want to replace with a parameter. My question is: Is this possible? How do I do it?

On a related note. I have noticed that when I open a query in design view one table is highlighted in yellow. All my queries are opening this way. What is the significance of the yellow box. I never noticed that before.

[ranman256]

user can fill controls on the form, then the code builds the 'where' clause for the base query.
the Xtab query runs off it.

'alter the query with your criteria....

'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef
sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery "qxCrosstab"
end sub

[dgmdvm]

???
I don't think you answer applies to my question. Wrong thread?

[ranman256]

No, i was fixing any problem in the base query via forms. If you want that way.

[dgmdvm]

The base query is not a problem, it works fine. I chose in this instance not to use querydefs. If I need a querydef to fix my problem with the expression field I will do so. My question remains, can you change the criteria of the Dcount expression in the crosstab query by using a parameter? If so, How does one accomplish it?

[Micron]

What is the significance of the yellow box. I never noticed that before.

Apparently is some sort of new feature in query grid and relationships window where you can 'highlight' several tables and move them as a group. Hasn't happened to me yet because I've turned off updates. Got tired of M$ "improvements"

As for your remaining question, I suppose you could define a query parameter (right click on query background) so that you get value(s) from unbound form controls that your existing code populates. Normally if I was coding all of this I wouldn't bother with a query as ranman seems to be saying. Might as well do it all from within a procedure.

[dgmdvm]

As for your remaining question, I suppose you could define a query parameter (right click on query background) so that you get value(s) from unbound form controls that your existing code populates

.

I created an invisible text box in my form , used VBA to enter a value equal to the entire criteria string, created a parameter and placed the parameter in my expression. It does work. I do agree it is a bit clunky.

Might as well do it all from within a procedure.

By this you mean creating a dynamic query for the report?

Thanks for the help.

[Micron]

By this you mean creating a dynamic query for the report?

That is a common approach. Usually requires that you deal with the possibilities, such as one or more controls not having a value, that a before value isn't greater than an end value - that sort of thing. Often the WHERE part of vba generated sql is just concatenated to the first part(s) so that the entire thing is made up of either the first part alone (if no criteria) or together with the criteria if those controls have values.