Day of the week

[staholly]

Is there some way I can determine what day of the week a date falls on within a query? Also, is there a way I can count how many wednsdays (for example) there is in a date range with in a query or is this a expression that only can be done with VBA, a form or a report?

 

[jdraw]

Probably. Can you give a specific example and we can work from that.

see http://www.techonthenet.com/access/functions/date/weekday.php

 

[thingssocomplex]

Not sure if this will help

Format([field],"dddd")

 

[staholly]

I need to be able to calculate how many saturdays and sundays in a date range to substract from the total days (for example: between 1/1/2011 and 3/11/2011 there is 12 sundays and 11 saturdays). I can do this in a form but I want to be able to do this in a query.

 

[ad.business]

Make a new table, call it tblDaySort, in the design view, make the first field called Days, data type of text and a 2nd field called DayNumber, data type number, (long integer). Save it and do not put an autonumber when it asks. Then open the table in datasheet view and populate it with the following information: In the first field, Type Sunday, then hit enter and type Monday in the Row below it all the way till Saturday. Then fill in your DayNumber column starting with 1 and then go down. Close the table, and open your current query. Put this in a new column: Day: Weekday([InsertFieldnameofyourDay]). Then save the query and open up a new query in design view, using the query that you just saved previously and then right click and hit show table. Bring in your table you just created, tblDaySort and create a relationship from tblDaySort.DayNumber over the field name in the query that has the day number. In this example, it would be Day. Then run the query and you should see the day, with the day of week next to in, then run a count of days and put your parameter criteria and you should be able to get what you are looking for.