UtterAccess Discussion Forums > Adding 12 Records

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

Kamulegeya

Nov 8 2011, 05:34 AM

Greetings UA friends

I want users to be able to add 12 records to table tblPeriods( PeriodNumber, Start, End). PeriodNumber start from 1 to 12. Period 1 have start of 1/0120... End of 31/01/...

Period 2 start of 1/02/... End of 28/02/.... We use UK time format.

I have come up with a crude way of doing it! Executing 12 Insert statements...like one below.

CODE

strSql = "Insert Into tblPeriods([PeriodNumber],[StartDate],[EndDate])Values(1,#" & CDate(DateSerial(Year(Date), 1, 1)) & "#,#" & CDate(DateSerial(Year(Date), 1, 31)) & "#)"

The above is for Period 1. ....

But i think there can be a better way of doing it.
Can some one kindly suggest a better way or how i can improve it

Ronald.

argeedblu

Nov 8 2011, 06:04 AM

You could use a loop to reduce the code to three statements:

CODE

dim bytPeriod as Byte

For bytPeriod = 1 to 12
strSql = "Insert Into tblPeriods([PeriodNumber],[StartDate],[EndDate])Values(1,#" & CDate(DateSerial(Year(Date), bytPeriod, 1)) & "#,#" & CDate(DateSerial(Year(Date), bytPeriod, 31)) & "#)"

'execute the action query here

next

Glenn

Larry Larsen

Nov 8 2011, 06:10 AM

Hi
Also try:

CODE

Dim mysql      As String
Dim lp         As Integer

    For lp = 1 To 12

        mysql = "Insert Into tblPeriods([PeriodNumber],[StartDate],[EndDate]) Values ('" & lp & "',#" & CDate(DateSerial(Year(Date), lp, 1)) & "#,#" & CDate(DateSerial(Year(Date), lp + 1, 0)) & "#)"

        CurrentDb.Execute mysql, dbFailOnError

    Next lp
End Sub

Your dates may also appear in a US format... Check out:International Dates in Access

Kamulegeya

Nov 8 2011, 06:40 AM

Awesome!!!!

It has worked ! My mind was thinking of a loop but i could not see how to use

Thank you Larry Larsen and argeedblu

My colleagues here think that i am an Access Geek!!! Thanks to UA!

Ronald

argeedblu

Nov 8 2011, 07:27 AM

Ronald,

You are most welcome. Glad you find UA helpful.

Glenn

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.