How do I use criteria to sort weekend days from week days?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count the weekend days in a simple query. I have formated the
date column to ddd but then try to enter a criteria such as = "Sat" with no
success. Anybody know how to do this?
 
Charlie, how did you have the date data in your table, with Capital "S" or
lower letter?. some time is case sensitive
 
Thanx lila, I tried both upper and lower case but get the message that the
expression is too complex to be evaluated. Without any criteria, the column
shows the day in 3 letter format (Sat) but won't work with any criteria.
 
It would help if you posted the SQL of your query. The simplest query I can
think of to do this would be

SELECT Abs(Sum(DatePart("w",[SomeDateField])=7))
FROM SomeTable
 
SELECT [NEW CREW QUERY].Date, [NEW CREW QUERY].Date, [NEW CREW
QUERY].Crewmember, [NEW CREW QUERY].[Trip ID]
FROM [NEW CREW QUERY]
GROUP BY [NEW CREW QUERY].Date, [NEW CREW QUERY].Crewmember, [NEW CREW
QUERY].[Trip ID];

This is the SQL view. I'm just trying to filter so it shows the weekend
days. Date is formatted ddd in the first one and and w in the second.

Thanx for any help.
John Spencer (MVP) said:
It would help if you posted the SQL of your query. The simplest query I can
think of to do this would be

SELECT Abs(Sum(DatePart("w",[SomeDateField])=7))
FROM SomeTable
I am trying to count the weekend days in a simple query. I have formated the
date column to ddd but then try to enter a criteria such as = "Sat" with no
success. Anybody know how to do this?
Click to expand...
Click to expand...
 
Put the following expression in a blank field of your query:
WeekEndDays:WeekDay([NameOfYourDateField]

Put this in the first row of the criteria:
1
Put this in the second row of the criteria:
7
 
I am trying to count the weekend days in a simple query. I have formated the
date column to ddd but then try to enter a criteria such as = "Sat" with no
success. Anybody know how to do this?
Click to expand...

Setting the Format property of a field does not change it from a
date/time field to a text field - just how it's displayed.

Instead, put a calculated field in a vacant Field cell in the query
grid:

DatePart("w", [datecolumn], 6)

This will return 1 for Saturday, 2 for Sunday, ... 7 for Friday. Put a
criterion of

< 3

on this field and you'll get just weekend dates; >= 3 will get just
workdays. Non-weekend holidays are a separate issue, of course.

John W. Vinson[MVP]
 
That did it. Thanks to yopu and to "pcdatsheet" for your help.

John Vinson said:
I am trying to count the weekend days in a simple query. I have formated the
date column to ddd but then try to enter a criteria such as = "Sat" with no
success. Anybody know how to do this?
Click to expand...

Setting the Format property of a field does not change it from a
date/time field to a text field - just how it's displayed.

Instead, put a calculated field in a vacant Field cell in the query
grid:

DatePart("w", [datecolumn], 6)

This will return 1 for Saturday, 2 for Sunday, ... 7 for Friday. Put a
criterion of

< 3

on this field and you'll get just weekend dates; >= 3 will get just
workdays. Non-weekend holidays are a separate issue, of course.

John W. Vinson[MVP]
Click to expand...
 
Back
Top