Date Issue
in [Help]
|
Prev: Date format issue on INSERT VALUE sql statement
Next: Access 2007 doesn't update table changes elsewhere
From: Odd on 27 Jul 2010 09:25 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
From: Bob Barrows on 27 Jul 2010 09:51 Odd wrote: > 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) 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: > DoCmd.RunSQL "INSERT into SelectedDates (SelectedDate) VALUES (#" > & iDate & "#)" > iDate = DateAdd("d", 1, iDate) > 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
From: Odd on 27 Jul 2010 09:55 On Jul 27, 9:51 am, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote: > Odd wrote: > > 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) > > 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: > > > DoCmd.RunSQL "INSERT into SelectedDates (SelectedDate) VALUES (#" > > & iDate & "#)" > > iDate = DateAdd("d", 1, iDate) > > 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 You're wonderful. Thanks!!!
From: paii, Ron on 27 Jul 2010 10:13
"Odd" <audrey.nsh(a)gmail.com> wrote in message news:8f82904a-31d2-46d1-9f7a-051a535a4dac(a)h2g2000vbf.googlegroups.com... > 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 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 |