How can I code ALL Teams on my report when querying all records.

[calientegg]

I have a report in Access 2010 based on SQL parameters.

The user inputs either a team number or * for all teams. The query will return the results for the selected team or all the teams based on user input.

What I need is the team to be displayed on the report.

I am using expression builder. I can get this to work for a single team, but not when the user inputs "*". It just displays 89, or the highest team in the query result.

This is what I have in expression builder:

=IIf(IsNumeric([Query1.Team]),[Query1.Team],"ALL")

I have tried using the name of the controls in place of Query1, I don't think this should be this hard.

The original report was created in 1995 in Access. I'm just trying to recreate this as a novice from a .mde version.

[NeoPa]

You're nearly there. You want :

=IIf(IsNumeric([Team]),[Team],'ALL')

The brackets ([ & ]) should surround each element of the reference. In this case the only element you need is Team. If you needed Query1.Team then it would be [Query1].[Team].

[strive4peace]

Better to test the criteria controls and only apply criteria if they are filled. Then you can specify the WHERE clause of OpenReport to limit it for the records that are specified.

here is an example that tests criteria and builds a criteria string to use as a parameter in OpenReport for the WHERECONDITION clause

DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]

ie:

    DoCmd.OpenReport "MyReport", acViewPreview,, varCriteria

WHERE
varCriteria is how to limit the records. For instance -->
"CustomerID = " & Me.CustomerID
OR
"PartID = " & me.PartID & " AND OrderDate =#" & me.OrderDate & "#"
OR
"Not IsNull( [fieldname] )"

varCriteria is the WHERE clause of an SQL statement without the word "where"

an advantage of testing controls as you construct the SQL is that you can ignore criteria that the user didn't fill out values to compare to

~~~
assuming you have are in the code behind your ReportMenu form... here is a general case:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

   'tell Access you are going to create a variable
   dim varFilter as variant
 
   'initialize the variable
   varFilter = null
 
   'substitute YOUR controlname in here after "Me."
   'we are testing to see if it is filled out
   'if it is, we are going to make varFilter hold the criteria
   If not IsNull(me.text_controlname ) Then
      varFilter = "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
 
   'test the next control
   If not IsNull(me.date_controlname ) Then
      'if we alread have criteria, add AND to specify that and more
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if
 
   'test the next control
   If not IsNull(me.numeric_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[NumericFieldname]= " & me.controlname_for_number
   end if
 
   if not IsNull(varFilter) then  
       DoCmd.OpenReport "ReportName", acViewPreview, , varFilter
   else
       DoCmd.OpenReport "ReportName", acViewPreview
   end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'SPACE UNDERSCORE at the end of a line means that the statement is continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited with ' or " (single or double quote marks ... double can be used anywhere; single has limitations in Access)

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a control for criteria is filled out.

If it is, we are going to see if we first need to add AND (if the filter string already says something)
Then we are going to add the criteria for that filter
varFilter = (varFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for the report -- and it is best for referenced fields to be in controls on the report (the Visible property can be No)

For a Date Range, you would do:

   If not IsNull(me.date1_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname]>= #" & me.date1_controlname & "#"
   end if
 
   If not IsNull(me.date2_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname] <= #" & me.date2_controlname & "#"
   end if
 

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
 
instead of
"#" & me.date_controlname & "#"
 

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~

   if len(trim(nz(Me.Filter,""))) > 0 then
      me.Label_Criteria.Caption = Me.Filter
      me.Label_Criteria.Visible = true
   else
      me.Label_Criteria.Visible = false
   end if

'~~~~~~~~~~~~~~~~~~

I have used
Me.Filter
as an easy way of showing the report filter (if there is one). Personally, I construct a friendly string showing report criteria to display in the page footer section

for the Difference between + and & ... well that is another long discussion. In a nutshell, anytime you use + then everything in the clause (or between parentheses) has to be filled out or the answer is null. Using & to concatenate gets whatever is there, null or not