Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report doesn't display (2000)

    If you select December 2003 in the startup form of the attached mdb and try to open the RFermiTotB report, a message pops up saying, "Expression typed incorrectly or too complex to be evaluated. A numerical expression, for example, can contain too many complex elements. Simplify by assigning parts of the expression to variables."
    Why then does the report record source(Query6) work perfectly?

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

    Re: Report doesn't display (2000)

    The problem is in the sums you calculate in the report footer; with the zillion Nz and IIf functions in your queries, they become too complicated. You can do without them, and simplify the conditions by requiring that Totale is not zero, instead of Nz([1],0)+Nz([2],0)+...+Nz([12],0).

    See attached version, with radically simplified queries.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Your query6 return text fields when 0.
    In your expressions
    1: IIf(nz([query5_2].[1];0)+nz([query5_3].[1];0)=0;<font color=red>""</font color=red>;nz([query5_2].[1];0)+nz([query5_3].[1];0))
    replace the "" by 0
    1: IIf(nz([query5_2].[1];0)+nz([query5_3].[1];0)=0;<font color=red>0</font color=red>;nz([query5_2].[1];0)+nz([query5_3].[1];0))

    If you don't want to see the 0 in the report, use the format of the textboxes in the report and set them to : <pre>#,#,"" or #;#;""</pre>

    depending on your international settings
    See attachment
    Francois

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Thank you Hans,
    The problem is without those Nz and IIf functions, the report record source fails to fetch all the data. If, in the attached mdb, you select December 2003 on the startup form and launch your query(Query6) and compare the return set with what you get with mine(Query61), you'll see that values are missing for February, March, September and October.

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

    Re: Report doesn't display (2000)

    In that case, reintroduce the Nz's in Query6, but - as Francois suggested - don't use the IIf's since they convert the values to text, making it impossible to calculate the sum:

    SELECT Query5_2.Descr0, Nz([query5_2].[1],0)+Nz([query5_3].[1],0) AS 1, Nz(query5_2.[2],0)+Nz(query5_3.[2],0) AS 2, Nz(query5_2.[3],0)+Nz(query5_3.[3],0) AS 3, Nz(query5_2.[4],0)+Nz(query5_3.[4],0) AS 4, Nz(query5_2.[5],0)+Nz(query5_3.[5],0) AS 5, Nz(query5_2.[6],0)+Nz(query5_3.[6],0) AS 6, Nz(query5_2.[7],0)+Nz(query5_3.[7],0) AS 7, Nz(query5_2.[8],0)+Nz(query5_3.[8],0) AS 8, Nz(query5_2.[9],0)+Nz(query5_3.[9],0) AS 9, Nz(query5_2.[10],0)+Nz(query5_3.[10],0) AS 10, Nz(query5_2.[11],0)+Nz(query5_3.[11],0) AS 11, Nz(query5_2.[12],0)+Nz(query5_3.[12],0) AS 12, query5_2.totale+query5_3.totale AS totale
    FROM Query5_2 INNER JOIN Query5_3 ON Query5_2.Descr0 = Query5_3.Descr0
    WHERE ((([query5_2].[totale]+[query5_3].[totale])<>0));

    To suppress zeros in the report, set the Format property of the text boxes the way Francois suggested.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Hi Francois,
    I've tried formatting the [1] text box in the report the way you suggested(please see attachment) but it gets changed into #,###,

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    See Attachment
    Francois

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Thank you so much Francois and Hans, #;#;"" is the right one <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.
    How do I format the text boxes so as to show the thousands separator?

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Use :
    #.###;-#.###;""
    Francois

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report doesn't display (2000)

    Thank you, how come the formatting doesn't work for the text boxes in the RFermi report in the attached mdb? You can see the numbers in the rightmost column still don't have the thousands separator.

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

    Re: Report doesn't display (2000)

    The format Francois posted assumes that a comma is used as decimal separator and a point as thousands separator (this is the setting he and I use on our Dutch language systems). If you use the point as decimal separator and the comma as thousands separator, set the format to

    <code>#,###;-#,###;""</code>

Posting Permissions

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