Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49

    Unanswered: how to query records that are over 24 hours old

    Here is one that I need some help with please.

    I have a query that I am using to pull records that are considered open. I need the query to pull the open records that are over 24 hours or 1 day old. I figured that the best way to do this would be to create a criteria on the date field. Now I have a date field and I have tried the following:

    >now()+1

    but it does not work.

    Is there a easy way? I do not need a report just the query.

    Thanks very much in advance for any and all suggestions.

    Richard.

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Richard

    How does what you tried not work?

    Is the field a 'true' date field?

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try:
    <Now()-1

  4. #4
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    LOL.

    Thanks so much. DUH! I should have used the '-' instead of the '+'.

    Today is just not my day.

    Once again this place rocks!!!

    Can I use a simple query to remove weekends now?

    Thanks.

    Richard.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is that the actual user requirement, or do they want only records that are more than one day old.

    now()-1 will give records that are 24hours old than the current time. the risk is that next time you run the report it may include other records. if your report is part of a suite then it is possible that you may have discrepancies appearing becasue of such timing issues.

    norammly I'd expect the requirement to be something like yesterday, close of business, rahter than 24 hours ago......
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    Quote Originally Posted by healdem
    is that the actual user requirement, or do they want only records that are more than one day old.

    now()-1 will give records that are 24hours old than the current time. the risk is that next time you run the report it may include other records. if your report is part of a suite then it is possible that you may have discrepancies appearing becasue of such timing issues.

    norammly I'd expect the requirement to be something like yesterday, close of business, rahter than 24 hours ago......
    Yes that is correct. I need to pull the records that are one business day old. Not 24 hours old. So anything that was done yesterday, not today. Or Friday not Monday.

    Is there a easy way to do this?

    Thanks.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try:
    < dateserial(year(now()), month(now()), day(now()))

    the datetime returned by dateserial() will be at time 00:00:00 of today.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and if you want to eliminate weekends (check the logical consequences of doing this before wasting the effort - if folk work on the weekends, you might get unintended results)

    < dateserial(year(now()), month(now()), day(now()) - iif(weekday(now(), vbsaturday) < 3, weekday(now(), vbsaturday), 0))
    currently using SS 2008R2

  9. #9
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    no one will be working the weekends.

  10. #10
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    this is what I came up with. It allows me to subtract the weekends if needed or just one business day. don't ask why my boss wants that but he does for his reports. Anyway here it is:

    <DateSerial(Year(Now()),Month(Now()),Day(Now()))-[num]

    with [num] being the number of days. I would enter 1 for tue through fri. 3 for mondays or holidays (i work for a bank.)

    Thanks very much for the help.

    This place rocks!

    Richard.

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Richard,

    there might a flaw in my w/e-remove suggestion (and in your similar-but-different proposed implementation).

    have a go on the Nth of the month and subtract X ( > N ) days
    -- i didn't check what A does with a negative day in dateserial() but it is probably unsafe to rely on Mr Gates' intuition.

    safer:
    build the dateserial() only with Now()
    use dateadd() to remove the required number of days. dateadd 'knows' to switch to an earlier month when needed:

    < dateadd('d', -X, dateserial(year(now()), month(now()), day(now())))



    sorry for this potential error in my second 'improved' suggestion

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jul 2005
    Posts
    144

    Unhappy 24 hr issue

    Quote Originally Posted by izyrider
    try:
    < dateserial(year(now()), month(now()), day(now()))

    the datetime returned by dateserial() will be at time 00:00:00 of today.

    izy
    Hi Izyrider,
    I have been reading this topic and looks like i am in the same boat with rmousir and i have been trying what you and others have suggested, but for some reason i am keep getting all the previous records as well??

    here is my query

    SELECT Issues.Title, Issues.[Opened By], Issues.[Opened Date], Issues.Status, Issues.Category, Issues.OrderNumber, Issues.PartNumber, Issues.Owner
    FROM Issues
    WHERE (((Issues.[Opened Date])<DateSerial(Year(Now()),Month(Now()),Day(Now()))) );

    I would like to see closed issues (records) for past 24 hrs only! Any issue that was closed 24hrs ago.

    Thanks

  13. #13
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    izyrider,

    I don't follow your last post.

    I also have re-read my post and I don't know if i clearly stated what I was/am doing. My group works with tickets that are opened through out the day. Any ticket that is not closed with in 48 hours is bad. So my project is to create a report that I can run daily to show which tickets have been opened for over 24 hours (half of our time to close them). This will allow me to keep on top of the team and warn them when they are getting close to the deadline.

    Your suggestion seems to work. I have also found that I can use:
    <now()-[num] with num being the number of days to not count (weekends, bank holidays, etc). So if a ticket was opened on Tuesday I don't care about it until Thursday. If the ticket is open on Friday I don't care about it until Tuesday.

    Could you please dumb down your second suggestion for me. I would defently like to use it if it will do a better job.

    Thanks very much for you help.

    Richard.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Richard: you are completely right!
    i misread my own typing and imagined i had written
    dateserial(....., day(now()) -N)
    which runs the risk of a negative day!


    dateserial(blah) -N
    works but it is cheating: it exploits the inner workings of the A datetime without guarantee that all future versions of A will use the same mechanism for storing datetimes. what i thought i had written would not have been cheating, but does not work

    the 'documented' way to do this is
    dateadd('d', -N, dateserial(blah))
    and you have the reasonable expectation that Mr Gates will provide backwards compatibility for dateadd() in all future versions of A.

    ...there is no obvious reason why Mr Gates should redefine how A stores dates: personally, I cheat.

    ranjah: Richard's requirement was to pull all previous records.
    if you want the past 24 hours and are prepared to cheat, try something along the lines of
    [Opened Date] between Now()-1 and Now()
    which, for the boring reason discussed above, should really be written:
    [Opened Date] between dateadd("d", -1, Now()) and Now()

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Jul 2005
    Posts
    144

    Unhappy

    ranjah: Richard's requirement was to pull all previous records.
    if you want the past 24 hours and are prepared to cheat, try something along the lines of
    [Opened Date] between Now()-1 and Now()
    which, for the boring reason discussed above, should really be written:
    [Opened Date] between dateadd("d", -1, Now()) and Now()

    izy[/QUOTE]

    Izy,
    I have tried the string above and got nothing out of it not sure why? I am attaching my dbase if you can please just take a look at my query and point me to the right direction i would really appriciate it.
    THanks again.
    Attached Files Attached Files

Posting Permissions

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