0

Hi I'm in design view for MS Access and I'm creating a query where it selects a list of patients admitted within the last 24 hours. I'm not sure how to do this for my DATE_ADMITTED column, Criteria field. Any help?

1 Answer 1

3

If the DATE_ADMITTED field is a real date/time field, then you can set the criteria of the DATE_ADMITTED field to:

>=Now()-1

That would filter the query results to the past 24 hours down to the second in which the query was run.

For the query to return yesterday and today use:

>=Date()-1

This should cover from yesterday 12:00:00 AM (midnight) to today 11:59:59 PM (midnight).

Of course the above criteria doesn't check for future dates/times. If on the off chance you need to ensure no future dates/times are included in the query results the two queries below will filter those out.

>=Now()-1 And <Now()

>=Date()-1 And <Date()+1

Website for criteria examples:
http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

Video Series (on Google Video):
http://video.google.com/videoplay?docid=-6002948507483364070&hl=en#

When you say last Friday, I take that the previous Friday (not the last Friday of the month or something).

Here is a query that will return the results for the previous Friday, and if executed on a Friday will still return the results of the previous Friday and not the current Friday.
(Weekday([DATE_ADMITTED])=6) And (DateDiff("d",[DATE_ADMITTED],Now())<=7) And (DateDiff("d",[DATE_ADMITTED],Now())>0)

If by 'from last Friday' you meant from the previous Friday until now then here are some criteria depending on the days you want included:

From current or previous Friday until now (if run on a Friday then will return only the current Friday results only)
Between Date() And Date()-(IIf((Weekday(Date())>=6),(Weekday(Date())-6),(Weekday(Date())+1)))

From previous Friday until now (if run on a Friday it will including current Friday in the results) (Includes current Friday if today is Friday.)
Between Date() And Date()-(IIf((Weekday(Date())>6),(Weekday(Date())-6),(Weekday(Date())+1)))

From previous Friday until now (unless run on a Friday, then will be from previous Friday until Thur). (Excludes current Friday, if today is Friday.)
Between Date()-(IIf((Weekday(Date())=6),1,0)) And Date()-(IIf((Weekday(Date())>6),(Weekday(Date())-6),(Weekday(Date())+1)))

NOT usage - Here is a website that show some examples of NOT.
http://www.fontstuff.com/access/acctut06.htm

Hope this helps.

11
  • 1
    It all depends on the definition of "last 24 hours" -- if it's a strict definition, your solution will work, but people often define precise terms like "last 24 hours" to mean something more general, like "within the last day" which loosely means more like >=Date()-1 (i.e., anything after midnight yesterday). Oct 1, 2010 at 22:56
  • 1
    +1, good point. Sometimes computer people can be too literal. :) Oct 2, 2010 at 3:18
  • My project description wasn't too specific on the 24 hours. Thanks Scott! Oct 3, 2010 at 6:14
  • 1
    The two ways I learned Access queries was by pressing F1 (which in 2010 is of no real help, unlike in 2000 or 2003), and using the Expression Builder. (in 2010 it is under Query Tools Design tab, Query Setup section, Builder). In the Expression Builder under Functions, Built-in Functions will be the keywords and usages. Clicking on the usage will pull up a help screen with examples at the bottom. I will have to research some sites, usually I would Google the keyword for additional help, but I'm sure there must be tutorial sites out there. If I find some I will post it. Oct 3, 2010 at 6:43
  • 1
    Correct NOT would exclude Friday vice include Friday. Sometimes when working with compound statements you would need to change the ANDs to AND NOT. Oct 5, 2010 at 16:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .