Accepting Correct Parameters from Query to Report (1 Viewer)

[proballin]

I am having trouble understanding what parameters are being passed from my query to my report. Currently I have the field name in a report title set to a field name (account type) from my query. The proper name appears based on the user input that I created in the query Criteria area. The only time the proper name does not appear is when the input is an "*" which selects all the account types. When the user inputs this in the criteria prompt the report shows just one account type. I'd like to be able to create an IIF statement that says if "*" is entered display "ALL" in the report title. However I do not know exactly what the query is passing to report.

I have been able to get around this by creating a form and reading from the form, however I'd still like to know what does a query pass to a report when you enter "*" as a criteria choice to select all.

 

[vbaInet]

How are you opening the report?

 

[proballin]

The report is opened through a macro which runs the queries first (asking for the input criteria) then opens the report.

 

[superen]

Sorry for interruption but I seem to have similar situation I have report which is opened through form and form parameters are defined in query like this [Forms]![Form_name]![Field_name] and now the report is opening only those records where value in form is equal to value in DB but I also need to open report with records apply to value like Smit* which can be Smith or Smith, Ginger, ... and so on.
so I wanted to know is it possible to define in query or form the posibility to open report with defining parameters in query or form with 'like' operator.

 

[Simon_MT]

This is what one solution I use a Function to create the criteria then use the criteria when opening forms or reports:

Function ClientsDialogue_Criteria()
    With CodeContextObject
        If .[Options] = 3 Then
            ClientsDialogue_Criteria = "[Client] in ( " & GetSelectedRecords & ")"
        ElseIf .[Options] = 4 Then
            ClientsDialogue_Criteria = "[Client Category] = '" & .[Cat1] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' or [Client Category] = '" & .[Cat2] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' or [Client Category] = '" & .[Cat3] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "'"
        ElseIf .[Options] = 5 Then
            ClientsDialogue_Criteria = "[Client Category] between '" & .[Cat1] & "' and '" & .[Cat3] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "'"
        ElseIf .[Options] = 6 Then
            ClientsDialogue_Criteria = "[Client Category] between '" & .[Cat1] & "' and '" & .[Cat3] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' and [Client Entry Date] between #" & .[FDate] & "# and #" & .[LDate] & "#"
        ElseIf .[Options] = 7 Then
            ClientsDialogue_Criteria = "[Client Category] between '" & .[Cat1] & "' and '" & .[Cat3] & "' and [Client Mail Status] Between '" & .[Status1] & "' and '" & .[Status2] & "' and [Client] like '" & .[Alphabet] & "*" & "'"
        End If
    End With
End Function

Function ClientsDialogue_Report()
    With CodeContextObject
            DoCmd.OpenReport "Clients Report", acViewPreview, "", ClientsDialogue_Criteria, acWindowNormal
        End If
    End With
End Function

This might be too much information!

Simon