You can get one particular day of the week from your formatted value, but not
which particular day. The easiest way to do this s first to create a table
which has two columns , one of your formatted dates over a range of years and
one of the corresponding real date on a chosen day of the week. The
following function is adapted from one of my own and will create such a table
automatically for you. Just pate it into a standard module and call it from
the debug window (aka the immediate window) which you can open by pressing
Ctrl + G. The final parameter array argument determines which days to
incluce in the table, so if you wanted Sundays you'd use 1, Mondays 2 and sp
on. Lets say you want to create a table of Mondays called MondaysCalendar
covering from the first Monday in 2008 to the last in 2017, you'd call it
with:
MakeCalendar_DAO "MondaysCalendar",#01/01/2008#,#12/31/2017#,2
Here's the function:
Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set dbs = CurrentDb
' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0
' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, calWeek CHAR(6)," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL
' refresh database window
Application.RefreshDatabaseWindow
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate, calWeek) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#,"""
& _
Format(Format(dtmDate, "wwmmyy"), "000000") & """)"
dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate, calWeek)
" & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#,"""
& _
Format(Format(dtmDate, "wwmmyy"), "000000") & """)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If
End Function
You can then simply look up the Monday date corresponding to your formatted
value with:
DLookup("calDate","MondaysCalendar","calWeek=""" & [CallDate] & """")
where [CallDate] is your formatted call date field, which would give you 11
February 2008, for 070208.
Ken Sheridan
Stafford, England
