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

C

CharlieG

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?
 
L

lila

Charlie, how did you have the date data in your table, with Capital "S" or
lower letter?. some time is case sensitive
 
C

CharlieG

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.
 
J

John Spencer (MVP)

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
 
C

CharlieG

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...
 
P

PC Datasheet

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
 
J

John Vinson

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]
 
C

CharlieG

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...
 
Top