Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Total Figures (2003)

    I have a report, which runs from a query, that gives me a view of jobs outstanding. The query is set up to give me just the outstanding jobs. What I would like is just a figure saying how many jobs there were to start with, and how many of these have been completed. Will I have to create another query to do this, and will I be able to incorporate this into my report.

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

    Re: Total Figures (2003)

    If your query only returns unfinished jobs, it won't tell you the total number of jobs. You can put a text box on the report (probably in the report footer) with a control source that used DCount to count the number of jobs, something like

    =DCount("*", "tblJobs")

    where tblJobs is the name of the table. You can put another text box in the report footer with control source

    =Count(*)

    to count all records returned by the query, i.e. the number of outstanding jobs. The difference between the two is the number of completed jobs.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    There's a date range involved. I only need the total number between two dates.

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

    Re: Total Figures (2003)

    You can add a where-condition argument to DCount:

    =DCount("*", "tblJobs", "JobDate Between #01/01/2004# And #12/31/2004#")

    or something similar.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    Thanks Hans. The date range is taken from the query (Start Date and End Date), so I've modified the condition and it's working fine. Thanks again.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    I was wrong. Now more dates have been added,it's not giving me the totals for the date range, but the whol table. Is this formula right?

    =DCount("*","tblInnovations",[Date] Between ([Start Date]) And ([End Date]))

    where [Date] is the field in the table and [Start Date] and [End Date] are the parameters of the Query

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

    Re: Total Figures (2003)

    All three arguments to the DCount, DLookup etc. functions are strings enclosed in quotes. If you want to include variable values in an argument, you must use concatenation. Try

    =DCount("*","tblInnovations","[Date] Between #" & Format([Start Date],"mm/dd/yyyy") & "# And #" & Format([End Date],"mm/dd/yyyy") & "#")

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    There may be occasions when a date isn't entered, so I need the full table counted. Can I put 'IsNull[Start Date]" in an Iff statement

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

    Re: Total Figures (2003)

    You can use the Nz function to replace null values with whatever you like. In this situation, replace [Start Date] with Nz([Start Date],#1/1/100#) and [End Date] with Nz([End Date],#1/1/3000#). This substitutes a date before the earliest possible start date and a date after the latest possible end date.

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    I've used

    =DCount("*","tblInnovations","[Date] Between #" & Format(Nz([Start Date],#01/01/100#),"mm/dd/yyyy") & "# And #" & Format(Nz([Start Date],#01/01/100#),"mm/dd/yyyy") & "#")

    and it's returning 0. Is the formula too complex?

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

    Re: Total Figures (2003)

    Please read my previous reply again. You're using Start Date twice now.

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    I'd cut and pasted twice. I've changed it to

    =DCount("*","tblInnovations","[Date] Between #" & Format(Nz([Start Date],#1/1/100#)
    ,"mm/dd/yyyy") & "# And #" & Format(Nz([End Date],#1/1/300#)
    ,"mm/dd/yyyy") & "#")

    and still get 0

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

    Re: Total Figures (2003)

    Please read my reply <post#=491677>post 491677</post#> [/i]carefully[/i] to see what you have done wrong.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Figures (2003)

    If you have a null in the End Date, that record is not going to get counted because the default end date you show is less than the start date. Try using this instead:

    Format(Nz([End Date], Date())
    Charlotte

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Total Figures (2003)

    My apologies. One little '0' makes a difference. Thanks again Hans

Posting Permissions

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