Looking through date in SQL

[Jon]

I have a form that is to be used to populate a table (tblBookings) of future
Bookings for a chosen weekday - that is selected by a choice of Checkbox's
(so they can have one or more days).

The user also chooses what year, the dates will be every chosen weekday for
that year, (from 1/1 if not this year, from today if this year).

I need to create a routine for each Weekday, but I can't figure all of the
looping and weekday bits!

So far I have:

Dim MonSQL As String
'Monday
If Me.Monday.Value = True Then
'populate all the entries
MonSQL = "INSERT INTO tblBookings ( CollectionDate, CollectionTime ) VALUES
(" _
& "#" _
& ThisWillBeTheCalculatedDateBit _
& "#" _
& ", '" & Me.CollectionTime _
& "' );"
DoCmd.RunSQL MonSQL
End If

Can anyone either advise on the correct way to loop through this and choose,
for this bit, the Mondays.

Or is there a better way to do this?

In anticipation, many thanks

Jon

[pietlinden]

whoa... I think you're going about this all wrong. This is about as
hard as falling down.

Fields on your form:
txtStartDate (formatted as a short date)
txtEndDate (ditto)
'if you want every day, use 1, if you want weekly, use 7.

Sub CreateDates(byval intNumDays)
dim dtTemp as Date
dim rs as dao.recordset

set
rs=dbengine(0)(0).openrecordset("tblBookings",dbAp pendOnly)
for dtTemp=cdate(txtStartDate) to cdate(txtEndDate) Step
intNumDays
rs.addnew
rs.fields("CollectionDate")=dtTemp
rs.update
next dtTemp

rs.close
set rs=nothing

end sub

as you can tell from the lowercase reserved words, I didn't test a bit
of this. But you're making your life much more difficult than it needs
to be. I never use RunSQL, because the one time I did in a loop, it
was a disaster.

hope this gets you started.

[Salad]

Jon wrote:

I have a form that is to be used to populate a table (tblBookings) of future
Bookings for a chosen weekday - that is selected by a choice of Checkbox's
(so they can have one or more days).

The user also chooses what year, the dates will be every chosen weekday for
that year, (from 1/1 if not this year, from today if this year).

I need to create a routine for each Weekday, but I can't figure all of the
looping and weekday bits!

So far I have:

Dim MonSQL As String
'Monday
If Me.Monday.Value = True Then
'populate all the entries
MonSQL = "INSERT INTO tblBookings ( CollectionDate, CollectionTime ) VALUES
(" _
& "#" _
& ThisWillBeTheCalculatedDateBit _
& "#" _
& ", '" & Me.CollectionTime _
& "' );"
DoCmd.RunSQL MonSQL
End If

Can anyone either advise on the correct way to loop through this and choose,
for this bit, the Mondays.

Or is there a better way to do this?

In anticipation, many thanks

Jon

You can cut and paste the following code into a module. Change the year
(intYear) and day (strDay) and run it. Look in the debug window for the
results. If acceptable, change strDay and intYear method to use the
values from the input form, comment out the Debug.Print, and uncomment
the method to add the records.

Sub CheckDate()
Dim intYear As Integer 'year entered
Dim intDay As Integer 'acSunday to acSaturday value
Dim intWeekday As Integer
Dim strDay As String 'day selected
Dim datHold As Date

'change these values for testing
strDay = "Tuesday" 'this will be from checkbox of day selected
intYear = 2007 'year selected by operator

Select Case strDay
Case "Sunday"
intDay = 1 'acSunday value...and so on
Case "Monday"
intDay = 2
Case "Tuesday"
intDay = 3
Case "Wednesday"
intDay = 4
Case "Thursday"
intDay = 5
Case "Friday"
intDay = 6
Case "Saturday"
intDay = 7
End Select

If intYear = Year(Date) Then
'get current date
datHold = Date
Else
'get the first of the year
datHold = DateSerial(intYear, 1, 1)
End If

'determine the day of the week respective of the day to use
intWeekday = WeekDay(datHold, intDay)

If intWeekday <> 1 Then
datHold = datHold + (8 - intWeekday)
End If

'what is the calculated date?
'see if the day and date match. comment out if acceptable
MsgBox Format(datHold, "Long Date")

'now run subroutine to insert dates
If Year(datHold) = intYear Then
'it's possible someone wants to add dates for this year
'around Christmas time and the next date for day specified
'would go into next year and that would not be correct

'uncomment out if msgbox above is acceptable
'AddDatesToTable datHold, intYear
End If

End Sub
Private Function AddDatesToTable(datLoop As Date, intYear As Integer)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblBookings", dbOpenDynaset)

Do While Year(datLoop) = intYear
'use debug to see which days will be inserted. Comment
'out debug line if not in test mode
Debug.Print datLoop

'update the record. uncomment to update
'rst.AddNew
'rst!CollectionDate = datLoop
'rst!CollectionTime = Me.CollectionTime
'rst.Update

'now add 7 days and loop
datLoop = datLoop + 7

Loop
rst.Close
Set rst = Nothing
End Function

[Jon]

Thank you both VERY much for the time taken to respond, I'm re-writing the
code right now!!!

Jon