Unexplainable problem with date loop/ append query

[Dan2kx]

Still duin the holiday database...

and i have discovered something that i cant explain... can you guys?

ok i have a procedure which basically (quite a longwinded process) selects the start date and the enddate from a table by the ID and then in VB selects that start date and end date (as a date) and loops until sdt > edt this loop (basically) appends to another table information for each date sequentially until sdt > edt.

this works fine... however...

i stumbled on a situation whereby it doesnt...

if the start date and end date bridge new year (eg 31/12/07 - 7/01/08)
then rather than add a single day it adds a month....(01/01/08, 01/02/08, 01/03/08)

i have watched the process in debug mode and the variables are set correctly and increment, it seems for some reason that the query fails...
i do have variable written as # " sdt " # and like i say it does work normally and the vb is correct...

any one explain that one?

[jaxjagfan]

Still duin the holiday database...

and i have discovered something that i cant explain... can you guys?

ok i have a procedure which basically (quite a longwinded process) selects the start date and the enddate from a table by the ID and then in VB selects that start date and end date (as a date) and loops until sdt > edt this loop (basically) appends to another table information for each date sequentially until sdt > edt.

this works fine... however...

i stumbled on a situation whereby it doesnt...

if the start date and end date bridge new year (eg 31/12/07 - 7/01/08)
then rather than add a single day it adds a month....(01/01/08, 01/02/08, 01/03/08)

i have watched the process in debug mode and the variables are set correctly and increment, it seems for some reason that the query fails...
i do have variable written as # " sdt " # and like i say it does work normally and the vb is correct...

any one explain that one?

Dim sdt as Date
Dim edt as Date
Dim dtRun as Date
Dim i as Integer
 
sdt = YourStartDate
edt = YourEndDate 
dtRun = sdt
Do While dtRun <= edt
"Do your appends and stuff"
dtRun = DateAdd("d", 1, dtRun)
Loop
 

This will run thru a loop adding a day until the dtRun = edt. You can use the dtRun in your queries and it shouldn't matter if it crosses years between StartDate and EndDate.

[missinglinq]

When asking for help with code that is failing, it's a good idea to actually post your code!

Linq ;0)>

[Dan2kx]

When asking for help with code that is failing, it's a good idea to actually post your code!

Linq ;0)>

well the actual code is quite long
i think i explained my problem quite well

but unfortunately jaxjagfan's solution doesnt solve the problem

so my code is basically (shortend of course)

dim sdt as date, edt as date, SDW as integer, MODD as integer
blah blah 
 
sdt = dlookup
edt = dlookup
do until sdt > edt
SWD = Weekday(sdt, vbMonday)
If SWD < 6 Then
CheckDateM = sdt - 2
MODD = -1
MODD = CheckDateM Mod 14
 
    select case modd
    modd 0
         sql update
    modd 1
         sql update 2
....................... Modd 2,3,4,7,8,9,10,
    modd 11
         sql update 11
   end select
end if
sdt = sdt + 1 (also tried) sdt = dateadd("d",1,sdt)
loop
 

i have watched the code run in debug mode and it works and displays the new date sequencially as required... it just doesnt exist the same way in the table, (table field set to date/time obviously and input mask is shortdate)

I have the same routine bound to a form and instead of variables i am taking the date values from the form txtboxes and that works just fine, its strange.
this routine runs from a different form

if i use the dates 31/12/07 to 04/01/08
this is what gets inputted... 31/12/07, 01/01/08, 01/02/07, 01/03/07, 01/04/07

[Dan2kx]

Any ideas?

Thanks

[Dan2kx]

does anyone have any ideas?

[Dan2kx]

Could it be because i am using in my append query the VB variable "AS Date" and then quoting it in the append query as #" & sdt & "# would it work if i quoted it as a string? '" & sdt & "'

[MMcCarthy]

Could it be because i am using in my append query the VB variable "AS Date" and then quoting it in the append query as #" & sdt & "# would it work if i quoted it as a string? '" & sdt & "'

This may have something to do with the date format.

Have a look at this article.

Literal DateTimes and Their Delimiters (#)

[Dan2kx]

I managed to solve it as such:

i set my sdt and edt variables and copied them as "Long"
in my append query i used rather than

#" & sdt & "#

i used

Format(" & sdt2 & ", 'dd/mm/yyyy')

if that makes sense

[llorracj]

I have a feeling that your problem is that Microsoft database systems expect the date to be in an American format (mm/dd/yyyy) and you are using the British system (dd/mm/yyyy)

[Dan2kx]

well it is weird, it works fine (the old way) except when you cross a new year (not sure about months... didnt check that out) but yeah that is probly the "main" reason (the problem only occured in queries), but hopefully i have fixed it, using a second variable as an integer (long) and then formatting that in the query!

Dan out