Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29

    Unanswered: 0 Record In A Report

    I HAVE A REPORT, IS THERE ANY WAY TO INSERT A 0 VALUE RECORD(BLANK RECORD) IF THE REPORT HAS NO DATA.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: 0 Record In A Report

    Originally posted by cwlord
    I HAVE A REPORT, IS THERE ANY WAY TO INSERT A 0 VALUE RECORD(BLANK RECORD) IF THE REPORT HAS NO DATA.
    Yes ... At what point do you want to do that?

  3. #3
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    I NEED TO GET ZERO VALUES INSTEAD OF #ERROR IN THE CALCULATED FIELD OF MY REPORT. BASICALLY BECAUSE THERE MIGHT NOT BE ANY DATA WITHIN THE REPORT THE REPORT IS BLANK AND I CANT MAKE CALCULATIONS WITH NULL VALUES.

    I NEED THE FIRST ENTRY TO BE HAVE ZERO VALUES INSTEAD OF BLANK.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by cwlord
    I NEED TO GET ZERO VALUES INSTEAD OF #ERROR IN THE CALCULATED FIELD OF MY REPORT. BASICALLY BECAUSE THERE MIGHT NOT BE ANY DATA WITHIN THE REPORT THE REPORT IS BLANK AND I CANT MAKE CALCULATIONS WITH NULL VALUES.

    I NEED THE FIRST ENTRY TO BE HAVE ZERO VALUES INSTEAD OF BLANK.
    And unfortunately you did not answer my question ... WHEN do you want to do this????? Before going into the report? After in the report?

    WHEN?

  5. #5
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    IM NOT SURE HOW TO DO THIS THE STRUCTURE OF MY REPORT IS;

    ACCOUNTS REPORT
    CASH IN SUBREPORT(BASED ON A QUERY)
    ELECTRONIC IN SUBREPORT(BASED ON A QUERY)
    CASH OUT SUBREPORT(BASED ON A QUERY)
    ELECTRONIC OUT SUBREPORT(BASED ON A QUERY)

    EACH SUBREPORT HAS TOTAL, TOTAL EX VAT, VAT. THE SUM OF EACH FIELD IS CREATED WITHIN EACH REPORT FOOTER. (IF REPORT HAS NO DATA REPORT FOOTER IS NOT SHOW, ALL BLANK)

    OUTSIDE THE SUBREPORTS WITHIN THE MAIN REPORT I ADD THE IN TOTAL AND OUT TOTALS TO GIVE ME OVER ALL TOTALS. IF ONE OF THESE SUBREPORT HAS NO DATA I GET #ERROR. BECAUSE IAM RUNNING THE REPORT BETWEEN TWO DATES IT IS POSSIBLE THAT THERE WILL BE NO DATA.

    I HAVE TRIED NZ FUNCTION BUT I THINK THIS DOES NOT WORK BECAUSE REPORT FOOTER IS MISSING AND THERES NOTHING TO VALIDATE.

  6. #6
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    SORRY IM NOT SURE OF YOUR QUESTION.

    EITHER , TO GET ONE RECORD WITH 0 VALUES IN THE TOTAL, TOTAL EX VAT AND VAT FIELD.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by cwlord
    SORRY IM NOT SURE OF YOUR QUESTION.

    EITHER , TO GET ONE RECORD WITH 0 VALUES IN THE TOTAL, TOTAL EX VAT AND VAT FIELD.
    I'm sorry you cannot understand my question ... I guess I can't help you because I don't know how you want to do it ...

  8. #8
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    Could I do this in the query the report is based on. I am not sure what I need to do.

  9. #9
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    Ok, I want to insert a zero record in a the query when it has no data.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by cwlord
    Ok, I want to insert a zero record in a the query when it has no data.
    How about this:

    In the Report_Open method of each report you put some code like this:

    Dim RecordCount as long, SQLString as string

    RecordCount=DCount("[Column of report]",Me.RecordSource)

    if recordcount =0 then
    SQLString="INSERT INTO " & Me.RecordSource & " ( list of columns ) VALUES (0,0,0,...,0);"
    docmd.RunSQL SQLString
    endif

    You'll nedd to enumerate each column name and provider the appropriate "0" value for each type ...

  11. #11
    Join Date
    Mar 2004
    Location
    Bournemouth, England
    Posts
    29
    Cheers thankyou

    I used this type of logic and got it to work. Its been a long long day. Now I can go watch last half of Chelsea v Arsenal.

Posting Permissions

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