Insert multiple records between a date range

[AllyFrog]

Hello,

New here - wondering if someone may be able to help me with an issue I'm having.

I have a table where I am recording details of shifts worked. Each record has the date of the shift, the start time, end time and other details. Most shifts are only one-offs so they can be entered individually, but every so often there is a series of them. I want to make it so that my user can enter the first date of the series, and the last date of the series, and for access to add records for each of the weekdays in between. I have created an unbound form to with 'startdate' and 'endate' fields as well as 'starttime' and 'endtime'

I found some code from a post on 'The Scripts' in 2006 (http://www.thescripts.com/forum/thread547400.html ) and have had a play with it but I can't get it to work - it adds the right number of records, but sets the date as back in 1899, rather than starting with the start date and ending with the end date.

If anyone could help me I'd really appreciate it.

Many thanks

[FishVal]

Hi, AllyFrog.

The example converts integer number to date. This means adding the number of days to 30-Dec-1899.
I suggest you to use While cycle and DateAdd function to iterate dates in predefined range. Also check the syntax of SQL expression. Date constants has to be enclosed in # signs.

[AllyFrog]

Hi, AllyFrog.

The example converts integer number to date. This means adding the number of days to 30-Dec-1899.
I suggest you to use While cycle and DateAdd function to iterate dates in predefined range. Also check the syntax of SQL expression. Date constants has to be enclosed in # signs.

Sorry to be a bother, but would you be able to give me an example (or point me to an example) of code using the While cycle and DateAdd function?

[FishVal]

Sorry to be a bother, but would you be able to give me an example (or point me to an example) of code using the While cycle and DateAdd function?

Hi, Ally. Sorry for delay.
The code will be something like this

Private Sub Ok_Click()
 
    Dim dteIterator As Date
 
    dteIterator = Me!StartDate
 
    While dteStart <= Me!EndDate
        If Weekday(dteIterator, vbMonday) <> vbSaturday And _
            Weekday(dteIterator, vbMonday) <> vbSunday Then
            DoCmd.RunSQL "INSERT INTO Hours(Worker, Date, Time) VALUES ('" & _
                Me!Worker & "', #" & Format(dteIterator, "mm/dd/yyyy") & "#, 8);"
        End If
        dteIterator = DateAdd("d", 1, dteIterator)
    Wend
 
End Sub