Hi everyone,

I need to be able to dynamically populate a table with the dates from
a specified period range.
For example, if Start Date = July 1, 2010 and End Date = July 31st,
2010. Then, the procedure below populates the table with dates from
July 1st to July 31st. However, Access is getting the first twelve
days of July wrong and is outputting January 7th, February 7th, March
7th, April 7th......all the way to December 7th. Then, when it hits
13...it then know it is July 13th.

Anyway, I am inputting my period range in the following fornat: yyyy/
mm/dd (Start Date = 2010/07/01 and End Date = 2010/07/31)

Here is the procedure.

Public Sub ResourceAllocation()

Dim sDate As Date
Dim eDate As Date
Dim iDate As Date

sDate = InputBox("Start Date")
eDate = InputBox("End Date")


iDate = sDate

DoCmd.RunSQL "DELETE FROM SelectedDates"

Do While iDate <= CDate(eDate)

DoCmd.RunSQL "INSERT into SelectedDates (SelectedDate) VALUES (#"
& iDate & "#)"
iDate = DateAdd("d", 1, iDate)

Loop


End Sub

Odd wrote:
You would probably be safer using hyphens instead of slashes, but I
guess this should work.
It sounds as if there is a conflict between the format that Jet is
expecting and the regional settings on your machine. You need to get
explicit with the format of the date strings you are creating to
concatenate into the sql string.

Instead of:
Try:

dim sql as string
Do While iDate <= CDate(eDate)
sql = "INSERT into SelectedDates (SelectedDate) VALUES (#" & _
Format(iDate,"yyyy-mm-dd") & "#)"
debug.write sql
DoCmd.RunSQL sql
iDate = DateAdd("d", 1, iDate)
Loop

--
HTH,
Bob Barrows

On Jul 27, 9:51*am, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote:
You're wonderful. Thanks!!!

"Odd" <audrey.nsh (AT) gmail (DOT) com> wrote

Your procedure ran on my system, Access97 US date settings.
I changed the output to the debug window, "debug.print iDate"
I enter the dates in the format you supplied.

Try changing you date localization to US?
Try compact decompile / repair / repair / compile?

PS: The InputBox lines can fail if a date is not entered.
Using the "Execute" method or a recordset should be much quicker then
RunSQL