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

[Gmgraham]

I have report that is based is on a query. The user is prompted to enter a value, the value the user enters prints out at the top of the report. This works fine when the user enters a single value (i.e. 13, 26, etc), but I can't get it to print the text "ALL" if the user enters * for all the records. I also am a self-study to all of this, so I know there is an easier, more efficient way to do this. This is in expression builder: (REDACTED DUE TO BUSINESS PURPOSES)



=IIf(Left([Reports]![Summary Report]![Query1.Team],1)="1*" Or "2*" Or "3*" Or "4*" Or "5*" Or "6*" Or "7*" Or "8*" Or "9*",[Reports]![Summary Report]![Query1.Team],IIf(Left([Reports]![Summary Report]![Query1.Team],1)="*","ALL"))


I welcome the advice of those willing to teach me a better way. Enlightment is so fulfilling.

[JamesDeckert]

=IIf(Left([Reports]![Summary Report]![Query1.Team],1)="1*" Or "2*" Or "3*" Or "4*" Or "5*" Or "6*" Or "7*" Or "8*" Or "9*",[Reports]![Summary Report]![Query1.Team],IIf(Left([Reports]![Summary Report]![Query1.Team],1)="*","ALL"))

I don't understand why you're checking for 1*, etc. Your description mentions entering a # or an *, but not both.
An If statement must be structured as IIF [something]="value". If you're going to add 'OR' or 'AND' you'll need to do, IIF [something]="value" OR [something]="another value", etc. You are ORing everything to the right of the equals before doing the comparison. You're probably never getting to the false part of the outer IIF. What is the purpose of the outer IIF?
Maybe the isnumeric function is what you want e.g. IIF (isnumeric(left[field],1)),[field], IIF ([field],"*","ALL",""))

[Gmgraham]

@Competent Performer, thank you for replying. Like I said I'm new to this, all the 1*, 2*, etc was my newbie attempt to make sure that whatever Team the user entered in the queried printed out. If they enter *, I want to print ALL. I tried your suggestion, I'm not sure if their is an issue with synatx, or me but I get an error: "You may have entered an operand without an operator" Here is what I substituted for what I previously had. Where did I go wrong?

IIF (isnumeric(left[Team],1)),[Team], IIF ([Team],"*","ALL","")) 

[JamesDeckert]

IIF (isnumeric(left[Team],1)),[Team], IIF ([Team],"*","ALL","")) 

you didn't put a ( when calling the left function, and the second IIF isn't checking for '='
try this
IIF (isnumeric(left([Team],1)),[Team], IIF ([Team]="*","ALL",""))

[Gmgraham]

Thanks again. At least that got it to print something out, I get a '#TYPE' printing in the field on the report now so something is off somewhere. Not sure if that is a design problem or what. I will keep at it.

[Gmgraham]

Hi @ Competent Performer. I got the #Type error to go away. Apparently I didn't give my control a new name (typical newbie faux pas), but it still doesn't recognize when I'm inputting the "*" into the query. It works fine forevery team. however, when I enter * it will print 89.

[orange]

You may get some ideas regarding what goes on behind the scene when dealing with "ALL" from this sample.
I realize this isn't a report example, but I'm just pointing out that you may have to intercept the user's input and do some special process to get the data you want.

[Gmgraham]

Hi @Orange, thank you for your reply. This is a little different than what I actually need. Or maybe I just don't know enough to make the connection between this and printing the criteria I need on my report. However, this is good stuff and I will keep it for my future learning however.

[JamesDeckert]

Is the statement you're trying to fix the control source for a control on a form/report, or part of a query? If a query, please show us the entire query. Where is the "*" being input?

[Gmgraham]

The query works fine outside of the report. The results you gave megive me a result now that I renamed my textbox control formerly named "TEXT" to "txtbx". (Duh!!) When I run the query, it will return the results for a particular team or it will reuturn the results for all the teams if I enter *. The problem comes in when I try to format the control "txtbx" on the report. This is what I'm asking help for. If the User enters a Team number in query, i want it print that number on the report, if they query all the records, I want it to print the word "ALL". The expression you gave me works for the teams when a number is entered, but it still isn't recognizing when I query all the records. i don't understand why, because the statement is logically sound as possible. If this true then.... if this then... false. What more does it need

[orange]

Can you post a copy of the database (zip format)?

[JamesDeckert]

When I run the query, it will return the results for a particular team or it will reuturn the results for all the teams if I enter *.

The expression you gave me works for the teams when a number is entered, but it still isn't recognizing when I query all the records.

These two statements seem to conflict. Going back to your original post, it looks like the query is getting the value from a control on a report. Is this right? Then the results of a query provide data for a different report? I'm confused, obviously. Can you show your query? Where is the team number entered?

[Gmgraham]

Unfortunately, due to business purposes. I cannot.

In a nut-shell, I have report based on a query. My query runs fine outside of the report. It returns records for a single team or all the teams if "*" is entered. All I want on the report is the Team number if the user enters a number in the query or the word ALL if * is entered in the query

[JamesDeckert]

Then how about on your report putting something like IIF((isnumeric([team]),[team],"ALL") for the control source of the field on the report?

[Gmgraham]

Ugh, same results. This doesn't makes sense. Why doesn't it see the second part of this statement? I don't get it.