Finding last date in a month from first date in month in access q.

[Guest]

I have an access query that gives me the current date, What I want is three
columns one with the 1st of the month one with the last date in the month and
the final column gives me the number of working days in that month.
for example

Start End Working Days
01/01/05 31/01/05 21

 

[Graham R Seach]

Darren,

Using code developed by Douglas Steele MVP:

SELECT [Start], [End],
DateDiff("d", [Start], [End]) -
DateDiff("ww", [Start], [End], 1) * 2 -
IIf(Weekday([End], 1) = 7,
IIf(Weekday([Start], 1) = 7, 0, 1),
IIf(Weekday([Start], 1) = 7, -1, 0)) As WorkingDays
FROM tblSomeTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Is it not possible in a select query with SQL?
Im confused about the From part of the sql
the information would be a query called Required Information Query

 

[Graham R Seach]

Daren,

OK, my mistake. What you want is this:

SELECT DateSerial(Year([somedate]), Month([somedate]), 0) As [Start Date],
DateSerial(Year([somedate]), Month([somedate]) + 1, 0) As
[End Date],
DateDiff("d", DateSerial(Year([somedate]),
Month([somedate]), 0),
DateSerial(Year([somedate]), Month([somedate]) + 1, 0)) -
DateDiff("ww", DateSerial(Year([somedate]),
Month([somedate]), 0),
DateSerial(Year([somedate]), Month([somedate]) + 1, 0), 1) *
2 -
IIf(Weekday(DateSerial(Year([somedate]), Month([somedate]) +
1, 0), 1) = 7,
IIf(Weekday(DateSerial(Year([somedate]), Month([somedate]) +
1, 0), 1) = 7, 0, 1),
IIf(Weekday(DateSerial(Year([somedate]), Month([somedate]) +
1, 0), 1) = 7, -1, 0)) As WorkingDays
FROM [Required Information Query]

---------
Change "somedate" to reflect the name of your date field.
---------

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia