Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF in Forms and Report (Access 2000)

    Hello:
    I have a field in a "Pledges" table that contains amounts of money contributed by members whose names are stored in the "Names" table. I would like to add a text box on a report and a form that shows the amount contributed by that person as a code. For example, if they contributed greater than 201 and less than 499 then the code will be "3". There will be 5 of these code levels. So if "AmountPledged" $0<>$99 ="1"; $100<>$199="2"; $200<>$499="3"; $500<>$1,000=4; >$1,000 = "5".

    I've built a query that has the [AmountPledged] field from the Pledges table and First name and Last Name fields from the Names table. The tables are linked on an ID field. I thought I would use this query as the source for the text boxes. Perhaps this is where my problem begins..

    My question is: I can't get the text boxes to work properly. They are in the detail sections of the form and report. The details sections contain the names of the members. Can someone please show me the IIF statement that I should be using?

    Also, is the IIF the best way to do this with 5 codes or is the Switch method better?

    Thank you,
    Doug

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Doug,

    I would write it like:

    iif([AmountPledged]<=99,"1",iif([AmountPledged]<=199,"2",iif([AmountPledged]<=499,"3",iif([AmountPledged]<=1000,"4","5"))))

    However, I have never thought you could use the "iif" statement in any structure other than a query. That's where I normally construct them. If you can use it directly in a form or report, then we both learn something...

    -- Jim

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF in Forms and Report (Access 2000)

    <P ID="edit" class=small>(Edited by charlotte on 26-Feb-04 15:16. corrected typo in function)</P>You can use IIF in controlsources and even in code, it just isn't necessarily the best practice. Normally in a report, you would create a calculated field in the underlying query and have it return the value. Probably a better choice in either a form or report would be to use the Switch function like this:

    Switch([AmountPledged]<=99,"1", [AmountPledged]<=199, "2", [AmountPledged] < 499, "3", _
    [AmountPledged]<= 1000, "4", [AmountPledged] >1000, "5")
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF in Forms and Report (Access 2000)

    In that case, I suspect there's something wrong with the way you entered it. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> I just corrected a previously unnoticed typo in the function. You might try fixing your controlsource along those lines first. If you still have problems, copy the string from the controlsource and paste it entirely into a post. Then we can see what went wrong. And by the way, this is for the controlsource of a calculated control. A form has a data source.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Charlotte:
    Thanks for the help.
    When I enter this function as the control source for a text box in the details section of my Names form, the text box shows the "Name#" error. I thought by setting the relationships in the relationships window to ID in both tables and one-to-many from Names to Pledges, the fields from the tables would be connected.
    Thank you,
    Doug

  6. #6
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Charlotte:
    I was modifying my post when you answered the old question. You should take a look at the new question.
    I entered the function as the control source of a text box in the details section of a form whose record source is the Names table.
    Here's the function as entered: Switch([AmountPledged]<=99,"1", [AmountPledged]<=199, "2", [AmountPledged] < 499, "3", [AmountPledged]<= 1000, "4", [AmountPledged] >1000, "5")
    Doug

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    I put the above function in the query and used the resultin in a text box on the forms and reports. It works well that way.
    Doug

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF in Forms and Report (Access 2000)

    If you see a #Name? in a control, it is because either there is a misspelling in your controlsource, or that field doesn't exist in the underlying recordset.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Charlotte:
    There's the problem. My main form is built from the Names table, not the Pledges query where the pledge code is built using the Switch function we talked about last night.

    I built a form from the query using only the code field, inserted it as a subform on the main form and it works fine, now. I don't want to do this on the report. I didn't really want to do this on the form, but it works on the form.

    My question is: how do I place a textbox in a form or report's detail section when the textbox's control source is different from the form or report?

    Thank you,
    Doug

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF in Forms and Report (Access 2000)

    You can:

    1. Create a subreport the same way you created a subform

    2. Add the table and the calculated field to the query underlying the report

    3. Use a domain aggregate function (i.e., DSum) in the controlsource of the control

    Probably someone else can suggest a few options I've forgotten about.
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Charlotte:
    I don't know how to add a table and/or query to an existing report or form. The relationships between the tables needed were already established in the main relationships window and both tables were already in the query I built using you Switch function. So what I did was to create a new report based on the query. It works fine now, but I thought I remembered a way to add a text box to an existing form or report the control source for which was not in the main form or reports record source.

    Thank for your help with the Switch function. It works in the query, but not in the form as a control source for a text box. It's because the field from the query is not part of the main forms underlying record source. I don't know how to add a table or query to an existing form or report. Building a new report worked in this case, but it's the long way 'round the barn.

    Doug

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF in Forms and Report (Access 2000)

    You can't "add a table or query to an existing form or report". You can add a table to the query that acts as record source of the form/report, and add one or more of its fields to the query design grid. Once you have saved the query, the extra fields will be available to the form or report. Charlotte mentioned several alternative ways to display data that are not part of the record source of the form or report in [post#348489] a little bit higher in this thread (and no, I can't think of other methods offhand.)

  13. #13
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in Forms and Report (Access 2000)

    Hans:
    Thanks for clearing that up. I didn't know a way to add a table to an existing form or report.

    The situation as it existed yesterday was that I had a form based on a table and a report based on a table. Neither were based on a query. So I eventually wrestled around with them and built a new form and a new report based on a query. The query contains a Switch function that returns a code that is used as the control source of a text box in the form and the report.

    Thanks for everyone's help in this matter,
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •