Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: iif statement or condition statement?

    Let’s say I have 3 groupings on my report. 1st business platform, 2nd businesses, and 3rd products, there are some cases where the business platform and the businesses (names are the same). I want to create an iif statement or set it up, so that when the name of the business platform = businesses, do not show.

    Anyone know how to do this?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Do not show what???? The platform or the businesses or both?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    One approach is to "seed the grouping" back in the query by adding a field that contains a number of letter that corresponds to each group, or a True/False to determine if the record should appear on the report. You can then use a variety of methods (including the iif statement) to populate this "seed field".

    In the report, group by the "seed field" (just don't show it on the report).

    tc

  4. #4
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    Do not show what???? The platform or the businesses or both?
    Oh I forgot to mention. Do not show the business (name), if it is the same as the platform's.

    THanks

  5. #5
    Join Date
    Nov 2006
    Posts
    67

    Iifstatement / conditional statement.

    Iifstatement / conditional statement.

    In this query, I have let’s say 4 number fields (1,2,3, and 4). I need to do calculation base on the 4 fields. So, I inputted a new field in the query to perform the calculation [ (2 * 3) / 4] (these are fields). The problem is that in column 4, there are blanks and zeros, and I cannot have zeros as a denominator. I am wondering, how to construct a ‘iif or conditional statement’ that states for every record in field 4 that is zero or null, do not do calculation.

    Thanks in advance.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tialongz
    Oh I forgot to mention. Do not show the business (name), if it is the same as the platform's.

    THanks
    Simple ... In the business platform groupheader (this is in VBA by the way), test the business platform name against the platform name if the same, cancel the grouprheader (by setting the Cancel parameter to not zero) ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    [(2*3)/Iif(NZ([4],1)=0,1,[4])] This will protect you by defaulting the denominator to 1 for your division (if 0 or empty/null) ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    oh,- I thought you wanted to omit the record entirely.

    In that case, take mike's advise - much simpler.

    tc

  9. #9
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    Simple ... In the business platform groupheader (this is in VBA by the way), test the business platform name against the platform name if the same, cancel the grouprheader (by setting the Cancel parameter to not zero) ...
    VBA? I am completely new to VBA. Is there a way like insert a textbox into the report and type an expression and make it work?

    Thanks in advance

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tialongz
    VBA? I am completely new to VBA. Is there a way like insert a textbox into the report and type an expression and make it work?

    Thanks in advance
    Nope. You have to suppress the groupheader programmatically ... Open your report in design mode. Right click and select view code. From the dropdown, select the groupheader ... Take it from there.
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    [(2*3)/Iif(NZ([4],1)=0,1,[4])] This will protect you by defaulting the denominator to 1 for your division (if 0 or empty/null) ...
    I followed your example, and tried to apply to a similar situation, but it didn't work out.

    =[(businesses – (564*products)) / iif(NZ[products],1)=0,1, [products])]

    Am I missing a bracket or .. too many?

    thanks in advance

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tialongz
    I followed your example, and tried to apply to a similar situation, but it didn't work out.

    =[(businesses – (564*products)) / iif(NZ[products],1)=0,1, [products])]

    Am I missing a bracket or .. too many?

    thanks in advance
    Looks like it ... What are the actual columns involved here?

    Try:

    =([businesses]-564*[pproducts])/ IIF(NZ([products],1)=0,1,[products])
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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