Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: report problem

  1. #1
    Join Date
    Jul 2006
    Posts
    108

    Unanswered: report problem

    ok, im trying to set an on_load event so that when a report loads if my "price" field is null or 0 then change that field to "---" but just as it displays on the report, i dont want the actual value in my table to change, just what it shows. can someone help me out?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Huh???? You want to "---" the report or the form?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2006
    Posts
    108
    ok my bad, here ya go:

    i have a report, and on that report i have several fields, some are null some arent, so im trying to setup an event so that when the report loads, if one of the fields is null, or equal to "00" to change it to "---" but dont change the actual stored value in the database, just the value on the report.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In the query/recordsource of the report, create a column and an expression like - FieldToShow: IIf(IsNull([MyField]) Or [MyField]=0,"---",[MyField]) then put that expression (field) on your report.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ah ... This one's simple ... Hide the textbox and put a label there over it. Set the label to "---". In the Detail Section, put code to test if the field is not empty and if so, set the label caption to that value ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Swatting flies with sledgehammers today Owen?

    There's a difference between a field containing the value 0 and the value 00. The first is a number, the second is a string. What is the datatype for your underlying field?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    So what's wrong with changing the expression in the query/recordsource to: FieldToShow: IIf(IsNull([MyField]) Or [MyField]="00","---",[MyField]) and then just put that field on the report? (it doesn't change any values in the table) Why go through the trouble of hiding a textbox and putting a label over it on the report and doing the same kind of test on the report and make the report do the work?
    Last edited by pkstormy; 10-06-06 at 14:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jul 2006
    Posts
    108
    i got it working guys, thank youl very much, it was just a single "0" sorry about that, it was a currency field BTW.

    thank agian guys!

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Swatting flies with sledgehammers today Owen?

    There's a difference between a field containing the value 0 and the value 00. The first is a number, the second is a string. What is the datatype for your underlying field?
    Nope ... Nukes. Had the somewhat same kinda issue on a report and that's what I did ... Except mine was a single "-" if there was 0 or no value ... It was a mimic of Excel.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pkstormy
    So what's wrong with changing the expression in the query/recordsource to: FieldToShow: IIf(IsNull([MyField]) Or [MyField]="00","---",[MyField]) and then just put that field on the report?
    Nothing IMHO - just some cat skinning going on I think
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pkstormy
    So what's wrong with changing the expression in the query/recordsource to: FieldToShow: IIf(IsNull([MyField]) Or [MyField]="00","---",[MyField]) and then just put that field on the report? (it doesn't change any values in the table) Why go through the trouble of hiding a textbox and putting a label over it on the report and doing the same kind of test on the report?

    Because this is more efficient...

    FieldToShow: IIf(nz([MyField], 0) = 0,"---",[MyField])
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'd agree with that Teddy (although I don't know if there would be any difference in the speed of the returned results - just seems like 2 different ways of writing the statement), but I would still do it as an expression in a query verses on the report. Does nz verses isnull really produce a difference in speed or is it just preference - how is it more efficient?
    Last edited by pkstormy; 10-06-06 at 14:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's not a matter of which one finds out whether the field is null or not faster, it's a matter of the number of comparisons involved.

    This is two scalar evaluations:
    IsNull([MyField]) Or [MyField] = "00"

    This is one:
    nz(myfield, "00") = "00"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    T-SQL doesn't support the nz function so I guess I've always gotten into the habit of using the isnull.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ISNULL() in TSQL is equivilent to nz() in access.

    Although I tend to use COALESCE in TSQL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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