Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Headache (2K)

    Hi,
    I have the attached database where I have become somewhat stuck on 2 reports.
    The reports are called RptAreaStatistics & RptStatistics. I keep getting error values in some of the fields and I've been rejigging the reports for about a week now with no result.
    I'm sure its a simple thing but something about wood and trees comes to mind.
    Any help would be vastly appreciated.

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    In rptStatistics your controlsource for txtAverage should be

    =DSum("qrymain.[Total Days]","QryAllLive") (note the square brackets)

    still looking at the other one...
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    Wow that bit was easy! Yet so hard...

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    I've got to do some work now <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    However I do notice that you have a text box on the report named Area and a field named Area - this will probably upset Access. You might try using the AreaNameID instead of the Area. Being numeric instead of text makes the syntax easier regarding quotes etc..

    Just a thought.

    Good luck!
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    Sorry you've lost me there.
    In RptAreaStatistics the error seems to be based around:

    =DSum("Total Days","QryAllLive","Area = " & Chr(34) & Forms!FrmMainAdded!Area & Chr(34))

    Does this need to be changed?

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    txtOver works with
    =DSum([Days Over],"QryDaysOverTarget","[AreaNameID]=" & Forms!FrmMainAdded!AreaNameID)

    txtDaysOver works with
    =DCount("*","QryDaysOverTarget","[AreaNameID]=" & Forms!FrmMainAdded!AreaNameID)

    txtAll works with
    =DSum([Total Days],"QryAllLive","[AreaNameID]=" & Forms!FrmMainAdded!AreaNameID)

    However you MUST remove the extra field Total Days in QryAllAlive or it blows up.

    Top Tip: make your life easier by not putting spaces in your field names - then you do not get bitten by needing square brackets.

    I think that fixes it.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    Hi,

    Sorry for the delay in getting back but been a tad busy.
    Thank you for the additional work, however I am still getting Error# codes on TxtOver, TxtDaysOver & now Text25! Arghh

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

    Re: Report Headache (2K)

    If you have * in a query, you must clear the Show check box for all columns you use to specify conditions and/or sort order, otherwise you'll have duplicate columns in the result.
    And put square brackets around field names that contain spaces.
    (Both have been pointed out by SteveH already)

    See attached version.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Headache (2K)

    I had removed the relevant ticks from the query and added the additional brackets. I've kinda copied your example of the report and it now seems to work okay, must have been a typo in my report.

Posting Permissions

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