Date Part function

  • Thread starter Thread starter Gary Nelson
  • Start date Start date
G

Gary Nelson

In Access2000,

I am trying to create a query that gives the number of orders received
within a select period of time not inclusive of weekends. Can someone tell
me how to do this in a query?
 
Create a column using the date part function set to
display the day (I think you can only set the date part to
display 1-7, 1 being Sun and 7 being Sat). In that column
set your criteria to be <>1 or <>0.

Hope this helps
Robert
 
Hi,


DatePart("w", DateTimeField) return the day of the week.


SELECT COUNT(*)
FROM somewhere
WHERE (OrderDate BETWEEN this AND 1+that)
AND (Datepart("w", OrderDate) BETWEEN 2 AND 6)


assuming Sunday (=1) and Saturday (=7) are the weekends; this and that are
the date defining the "period" of time. Note that in the absence of
supplying a time, the time is considered to be midnight, that is why I added
24hours, 1 day, to "that", in order to include all the day, not just its
first second.





Hoping it may help,
Vanderghast, Access MVP
 
Thanks - I tried the DatePart("w",[DaysDiff]) and in the criteria placed <>1
or <>0 and came up with an empty resultset. Please see below the fields I
currently have in place.

JobNum
DateIn - Criteria: Between [Beginning Date] And [Ending Date]
ShipDate
DaysDiff: [DateIn]-[ShipDate]
OnTime: IIf([DaysDiff]<=-3,"False","True")
Weekday: Weekday([DaysDiff]) Criteria: <>1 And <> 0

Here's what I am attempting to accomplish. We receive jobs in from
customers that need to be processed and shipped with three days. I am
attempting to measure the on-time rate of successful shipments within a
select period of time. I have created the query to the point where the user
can enter a date range. Retreive the number of days it took to get the
order received and shipped. However, I have not been able to exclude
weekends from the resultset. Which means that our on-time rate will
decrease with orders received on Friday and shipped Mon or Tues. Therefore,
I need to account only for weekdays.

I hope this provides some clarification.
 
Hi,


Use the function dhCountWorkdaysA in
http://www.mvps.org/access/datetime/date0012.htm.


Hoping it may help,
Vanderghast, Access MVP


Gary Nelson said:
Thanks - I tried the DatePart("w",[DaysDiff]) and in the criteria placed
Click to expand...
or <>0 and came up with an empty resultset. Please see below the fields I
currently have in place.

JobNum
DateIn - Criteria: Between [Beginning Date] And [Ending Date]
ShipDate
DaysDiff: [DateIn]-[ShipDate]
OnTime: IIf([DaysDiff]<=-3,"False","True")
Weekday: Weekday([DaysDiff]) Criteria: <>1 And <> 0

Here's what I am attempting to accomplish. We receive jobs in from
customers that need to be processed and shipped with three days. I am
attempting to measure the on-time rate of successful shipments within a
select period of time. I have created the query to the point where the user
can enter a date range. Retreive the number of days it took to get the
order received and shipped. However, I have not been able to exclude
weekends from the resultset. Which means that our on-time rate will
decrease with orders received on Friday and shipped Mon or Tues. Therefore,
I need to account only for weekdays.

I hope this provides some clarification.

Michel Walsh said:
Hi,


DatePart("w", DateTimeField) return the day of the week.


SELECT COUNT(*)
FROM somewhere
WHERE (OrderDate BETWEEN this AND 1+that)
AND (Datepart("w", OrderDate) BETWEEN 2 AND 6)


assuming Sunday (=1) and Saturday (=7) are the weekends; this and that are
the date defining the "period" of time. Note that in the absence of
supplying a time, the time is considered to be midnight, that is why I added
24hours, 1 day, to "that", in order to include all the day, not just its
first second.





Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top