Substitute the text "ALL" on the report when the user selects all records (*) in a query

[JamesDeckert]

Is your query IIF in the criteria row, or is it on the Field name row?

[Gmgraham]

The IIF statement you've been helping me with is in the expression builder. It is attached to the control source of the text box.

[Gmgraham]

@ Competent Performer, I'm sorry, I didn't see this post and I did not get a chance to respond to it. I see your confusion. Actually it is the opposite. The report is getting the values from the query. The user is prompted for the start and end dates and the team, then the report is generated from the underlying query.

[JamesDeckert]

So the IIF statement is referring to [team] (the field name returned by the query)?
Is [team] a field from the underlying table, or is it a calculated field?
If it's the field name from the table, it won't contain '*'. It'll be the team number for each record.
If it's the value input by the user, then it'll contain whatever the user input.
When you run the query by itself, after inputting that you want all teams, what is in this field?
Is it possible to include a screen shot of the query results?

[Gmgraham]

@ Competent Performer, unfortunately I cannot provide a screen shot. To answer your questions, [team] is field in the underlying table, it is not calculated.
When the user input * into the query to select all the teams, the results return the numbers 01-99.
Is there a way to say IIF [team] "includes all the numbers in the set 01-99". "ALL"

[Gmgraham]

@ Competent Performer, unfortunately I cannot provide a screen shot. To answer your questions, [team] is a field in the underlying table, it is not calculated.
When the user inputs * into the query to select all the teams, the results return the numbers 01-99. If the user inputs just one team number, it will only return the results for that particular team 01, or 02, etc.... and the that Team Number correctly prints at the top of the report.
Is there a way to say IIF [team] "includes all the numbers in the set 01-99", then print the word "ALL" or "ALL TEAM"? Can I use the COUNT function or something like that? I know I'm making this way too hard. Your help has been much appreciated.

[JamesDeckert]

@ Competent Performer, unfortunately I cannot provide a screen shot. To answer your questions, [team] is a field in the underlying table, it is not calculated.
When the user inputs * into the query to select all the teams, the results return the numbers 01-99. If the user inputs just one team number, it will only return the results for that particular team 01, or 02, etc.... and the that Team Number correctly prints at the top of the report.
Is there a way to say IIF [team] "includes all the numbers in the set 01-99", then print the word "ALL" or "ALL TEAM"? Can I use the COUNT function or something like that? I know I'm making this way too hard. Your help has been much appreciated.

I don't understand when you say it includes all numbers? Do you mean that it returns A number between 01-99?
If so you can do iif([team]>=1,iif([team]<=99,"ALL","too high"),"too low")

[Micron]

I might have a suggestion, but it depends on how the user is being prompted to input the query criteria. I presume that your procedure is to open the report and you're letting that open the query behind the report. This would mean you must have a parameter in the criteria grid or in the query properties, and that is causing the parameter prompt. It may be possible to 'get' the parameter that is being fed into a query, but it exceeds my programming knowledge, and I was not able to find anything on the subject. If I've guessed the process, this is not how I would do it.

Rather, a form button should open the report if the user makes a selection in a combo box with the team values. There is more than one way to get "ALL" into this combo box (Value List or have ALL as a record in tblTeams would be the two easiest). Pass the selection to the report openArgs, and in the report load event, set the value of your unbound text box control accordingly. Sounds more difficult than it really is. For example, for the button click event on the form that opens the report:
[syntax for this - OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)]

Private Sub cmdOpenReport_Click()
Dim strTeam as String

strTeam = Me.cmbTeam 'this is the combo box holding the team value
If IsNull (strTeam) Or strTeam = "" Then 
  msgbox "You must select a Team."
  Exit Sub
End If

Docmd.OpenReport "rptTeams",,,,strTeam
End Sub

In the report Load Event (you might be able to use the Open event, but affecting data is often best done in the load event).

Private Sub Report_Load()
Me.txtTeam = Me.OpenArgs 'txtTeam is the textbox control that identifies the team in the report (header?)
End Sub

I don't get why you put a * after team numbers, so I can't anticipate what needs to be done with that, if anything.

[Gmgraham]

Hi,
Thank you for replying. The "*" is part of the query that allows the user to selct all the teams. The user is prompted for the parameters from a text box. Then query is run and the report is open. Will the above still work?

[Gmgraham]

@ JamesDeckert wrote:
I don't understand when you say it includes all numbers? Do you mean that it returns A number between 01-99?
If so you can do iif([team]>=1,iif([team]<=99,"ALL","too high"),"too low")


Hi Mr. Deckert,
I mean the query can return up to 99 numbers (Team 01 -Team 99) based on the criteria entered in the parameter . Somehow I have to get it recognize the "different instances" of team numbers. This is where I'm having trouble coming up with up a function or an expression to make it work.

[Micron]

Will the above still work?

If you're addressing me, yes but I think you will need modifications since my answer was based on how to set the value of the report textbox. You seem to be having issues on how to drive the process before that point, so I'll elaborate.

My assumption is that a user would have to select a team number from the combo. However, I don't recall if you ever said what the data type is for Team - it could be text even though it looks like a number. Since you're looking for an ALL option, I presumed Team data is text type. If it is not, there is more than one way to solve this.
1) a query for the selected team that references the form combo selection (Forms!frmFormName.cmbTeamComboName). Another query just gets records for all teams. Then two command buttons on the form to open the report. One caption says Selected Team Report. On that click event you run Private Sub cmdOpenReport_Click() as before (but add the code for checking if the report is already open). The other caption is All Teams Report. For this click event, you change the open argument to "ALL".

Private Sub cmdOpenReportAll_Click()
If CurrentProject.AllReports("rptYourReportName").IsLoaded Then
  Docmd.Close acReport,"rptYourReportName"
End If
   Docmd.OpenReport "rptTeams",,,,"ALL"
End Sub

I'm going to alter the report recordsource here, so I would try moving the report code to the Open event, not the Load. It would also be prudent to ensure the report is not already open before changing the recordsource (see added code above).

Private Sub Report_Open()
If Me.OpenArgs = "ALL" Then
   Me.Recordsource = "qryThatGetsRecordsForAllTeams"
Else
   Me.Recordsource = "qryThatGetsRecordsForOneTeam"
End If
Me.txtTeam = Me.OpenArgs 'this part MIGHT have to be put in the Load event.
End Sub

2) Option buttons in a Frame is another way. You'd still need everything outlined above except only one button to open the report. You use the value property of the option group (frame) to decide which report openArg to pass. What I have not done is include any error trapping because I don't know the rest of your code structure.

[Gmgraham]

If you're addressing me, yes but I think you will need modifications since my answer was based on how to set the value of the report textbox. You seem to be having issues on how to drive the process before that point, so I'll elaborate.

My assumption is that a user would have to select a team number from the combo. However, I don't recall if you ever said what the data type is for Team - it could be text even though it looks like a number. Since you're looking for an ALL option, I presumed Team data is text type. If it is not, there is more than one way to solve this.
1) a query for the selected team that references the form combo selection (Forms!frmFormName.cmbTeamComboName). Another query just gets records for all teams. Then two command buttons on the form to open the report. One caption says Selected Team Report. On that click event you run Private Sub cmdOpenReport_Click() as before (but add the code for checking if the report is already open). The other caption is All Teams Report. For this click event, you change the open argument to "ALL".

Private Sub cmdOpenReportAll_Click()
If CurrentProject.AllReports("rptYourReportName").IsLoaded Then
  Docmd.Close acReport,"rptYourReportName"
End If
   Docmd.OpenReport "rptTeams",,,,"ALL"
End Sub

I'm going to alter the report recordsource here, so I would try moving the report code to the Open event, not the Load. It would also be prudent to ensure the report is not already open before changing the recordsource (see added code above).

Private Sub Report_Open()
If Me.OpenArgs = "ALL" Then
   Me.Recordsource = "qryThatGetsRecordsForAllTeams"
Else
   Me.Recordsource = "qryThatGetsRecordsForOneTeam"
End If
Me.txtTeam = Me.OpenArgs 'this part MIGHT have to be put in the Load event.
End Sub

2) Option buttons in a Frame is another way. You'd still need everything outlined above except only one button to open the report. You use the value property of the option group (frame) to decide which report openArg to pass. What I have not done is include any error trapping because I don't know the rest of your code structure.

I see that you changed the code so my report would rely on two queries instead of one? I think for my level that's getting too far away from the query's simple design: input a team number for one team -get the information for one team or input * for all the teams, get the information for all the teams (0-99). All I want to do is translate the input of the * in the query into the phrase "ALL Teams" on the report.

[Micron]

For me, the problems are this. None of the assumptions or questions I've raised in post 23 and 26 have been answered. On top of that, the fact that this goes back a ways in time and I do try to help out others as well makes it difficult to stay on one track. I still don't even know if the team data type is text or number. AFAIK, you cannot use a wildcard character on a number field. You can coerce numbers to text and use the result on a text field, but I don't see that as being applicable here - again, unless maybe team is text data type. If it's not, you're stuck between a rock and a hard place. While the code I put together may seem daunting to you and far removed from what you envisioned, I do not see how else you can accomplish what you want based on my presumption that team data is a number.

Coding help is here if you want it, but you will have to let your guard down a bit (I presume that you cannot post screen shots because of some sort of privacy concerns). A db version with just your applicable form, query, report and table with dummy data should not be a problem, but that's just my opinion. Should you decide to do that, it will have to be a 2007 version for me to help.