Hello Jim
Something like this...
Public Function AppendWeekdays(SomeDate as Date)
Const TableName = "Name of your table"
Const FieldName = "Name of your date field"
Dim rs as DAO.Recordset
Dim dt as Date
Dim yy as Integer
Dim mm as Integer
yy = Year(SomeDate)
mm = Month (SomeDate)
CurrentDb.OpenRecordset("Select [" & FieldName & "] from [" & TableName _
& "] where Year([" & FieldName & "])=" & yy & " and Month([" & FieldName
& "])=" & mm)
dt = DateSerial(yy, mm, 1)
Do While Month(dt) = mm
if WeekDay( dt, vbMonday ) < 6 Then
rs.FindFirst "[" & FieldName & "]=" & Format(dt, "\#yyyy-mm-dd\#")
if rs.NoMatch Then
rs.AddNew
rs(FieldName) = dt
rs.Update
End If
End If
dt = dt + 1
Loop
rs.Close
End Function
Just pass the function any date in the month you wish to add - for example:
Call AppendWeekDays( Date() )
will do the current month.
The code checks first to see if the dat is already in the table before
adding it.
HTH!
Cheers,
Graham
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Friday, 1 July 2011 05:45
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Generate table with dates in a specific month
excluding weekends
Hello All,
Is there a way to generate a table with all the dates in a month excluding
weekends?
Currently users are using excel to generate the table. The auto-fill is
quick. The saturday and sunday dates are deleted pretty quickly. Then I use
that spreadsheet as a linked table. But there has to be a quicker way in
Access to get the dates.
Thank You
Jim Wagner
Tidak ada komentar:
Posting Komentar