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

    Report has abnormally high stats (2000)

    Hi,
    I have the following Expression in a query I have that should only return details of files where DateOut is in the previous month. Could anyone advise me whether this has been done correctly as it seems to be returning abnormally high statistics for a report that it relates to.

    IIf(IsNull([DateOut]),#01/01/1900#,DateSerial(Year([DateOut]),Month([DateOut]),1))

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report has abnormally high stats (2000)

    Nigel,

    If you want data from the prior month, then you need to subtract 1 from the month as follows:

    IIf(IsNull([Invoice Date]),#01/01/1900#,DateSerial(Year([Invoice Date]),Month([Invoice Date])-1,1))

    Your formula will then return the 1st day of the prior month.

    However, you state you want to return details of files where date out is in the previous month. As your formula only returns the 1st day of the previous month, do you need to include the remaining days as well? If so, you could modify the query to compare year and month fields only between the two fields or use a between statement to get data that falls within the month.

    HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: Report has abnormally high stats (2000)

    Thanks Gary,
    What I need to be able to do is produce statistics for the previous month dependant upon whether Date Out is within that month. So, for August, it would be all files that Date Out fall within 1st August to 31st August.
    I confess that I'm still on a steep learning curve with Access and its not a quick journey! <img src=/S/blowup.gif border=0 alt=blowup width=60 height=60> And I'm not too sure how to 'phrase' the expression in the query.
    Further help would be very appreciated!!

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

    Re: Report has abnormally high stats (2000)

    I'm not clear on how you query is structured, but if you want the information from a month, you need only test for the month like this: Month(Nz([MyDate],0)) = 8
    Charlotte

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

    Re: Report has abnormally high stats (2000)

    The database records material that is sent out within a certain deadline dependant upon what type of file it is. So, for arguments sake, file A has a deadline of 10 days from the date of receipt, to the date that the material relating to the file is sent out. File B has a 14 day deadline, same reason.
    What my query(QryMain) collates is the data from a single table (tblMain), of all the files and whether or not they've missed their target. I then need an expression in this query that pulls all the data that has a date in Date Out relating to any period within the previous month. Then on a monthly basis I'm hoping I can click a control that produces the report.
    I basically need a formula that will calculate all the Date Out's that fall within the previous month to the current one.
    I think that makes sense!

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

    Re: Report has abnormally high stats (2000)

    That is true. I was merely pointing out that using DateSerial to get a month is the long way around.
    Charlotte

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

    Re: Report has abnormally high stats (2000)

    Thanks for that, thinking about it this would have been the logical way to do it.
    I have used the Month(Now())-1 and Year(Now()), which seems to display the corract statistics.
    Month(Now())01, didn't seem to work, so I presumed 01 was meant as -1.
    Still have alot to learn with Access, but thnk you for your help.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report has abnormally high stats (2000)

    If you have more than one year of data in the table you DO have to check both the year and the month

    Easiest method I can think of is to have two columns in your query:

    monthout: month([DateOut])
    and
    yearout: year([DateOut])

    criteria for yearout column is Year(Now())
    criteria for monthout column is Month(Now())-1

    That will use the date as of now and select the fields where DateOut is the same year and last month

    If you need to have data input to select the year/month, replace now() with [ReportDate]

Posting Permissions

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