Query in MS Access 2010: “patient date admitted within the last 24 hours”, what is the criteria for this?

Question

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?

Answer 1

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.