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

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

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