Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Thumbs up Unanswered: Query-by-Form IIf Statement Problem

    Hi everyone! I'm running a query-by-form to get reports from my database of special even functions and I'd like to generate reports by the count of people attending these functions (called the PAX count). In my table, I have a column for the PAX number which is either inputted or left blank depending on the information given. So when I designed my query-by-form, I created two text boxes where the user can specify a range to find data by pax. The first text box would be the beginning number (say 400) and the next would be the last number (say 6000). In any case, I figured I'd have to use an IIf function for this to work, but I can't seem to get it to work at all. My scripting for the underlying query is:

    Code:
    IIf([Forms]![Query Form]![Text19] And [Forms]![Query Form]![Text21] Is Null,[Forms]![Query Form]![Text19] And [Forms]![Query Form]![Text21] Is Null,Between [Forms]![Query Form]![Text19] And [Forms]![Query Form]![Text21])
    I always generate a report with no records and am confused as to what I'm doing wrong with my scripting. Can someone help me???

  2. #2
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I can't really totally help you but I'll give you an example.

    Code:
    IIf([lastName]="pasty",[firstname]+" "+[lastname],"Nobody is named Pasty")
    in yours you say if Text19 and Text21 is Null (or like 0) set them to 0. Then you are looking at 0 between 0 = 0 hits. That what it looks like to me...For kicks try

    Code:
    IIf(([Text19] AND [Text21]) IS NULL, ....
    Why didn't you use a simple parameter query like here http://support.microsoft.com/default...b;EN-US;304428
    Ryan
    My Blog

  3. #3
    Join Date
    Jul 2004
    Posts
    6
    The reason, I guess, for the Null scripting is because I want the user to be able to not enter anything, if they don't have a PAX count in mind, and view all the records no matter what PAX count.

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    So, did you check that link out? if you choose null and null you get all records. of course you will need to customize it. Have you tried just using a between statement for the criteria field under PAX in design view?
    Something like:
    Code:
    Between [Enter low value] AND [Enter High value]
    when you run it, it will ask you what rang you want to cover...
    Ryan
    My Blog

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I am not sure what you are trying to do. If Text19 and Text21 are both null, then what do you want to show? If only Text19 is null, then what do you want to show? Similarly, if only Text21 is null, then what do you want to show? I usually use the nz or the isnull functions in an IIF statement.

    Try this:

    IIf(isnull([Forms]![Query Form]![Text19]) And isnull([Forms]![Query Form]![Text21]), <put your result when true>, <put your result when false>)

    If the Query form is not running, then you will be asked for parameter values.

Posting Permissions

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