By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Unexplainable problem with date loop/ append query

100+
P: 365
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?
Dec 31 '07 #1
Share this Question
Share on Google+
10 Replies


jaxjagfan
Expert 100+
P: 254
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?
Expand|Select|Wrap|Line Numbers
  1. Dim sdt as Date
  2. Dim edt as Date
  3. Dim dtRun as Date
  4. Dim i as Integer
  5.  
  6. sdt = YourStartDate
  7. edt = YourEndDate 
  8. dtRun = sdt
  9. Do While dtRun <= edt
  10. "Do your appends and stuff"
  11. dtRun = DateAdd("d", 1, dtRun)
  12. Loop
  13.  
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.
Dec 31 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
When asking for help with code that is failing, it's a good idea to actually post your code!

Linq ;0)>
Jan 1 '08 #3

100+
P: 365
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)

Expand|Select|Wrap|Line Numbers
  1. dim sdt as date, edt as date, SDW as integer, MODD as integer
  2. blah blah 
  3.  
  4. sdt = dlookup
  5. edt = dlookup
  6. do until sdt > edt
  7. SWD = Weekday(sdt, vbMonday)
  8. If SWD < 6 Then
  9. CheckDateM = sdt - 2
  10. MODD = -1
  11. MODD = CheckDateM Mod 14
  12.  
  13.     select case modd
  14.     modd 0
  15.          sql update
  16.     modd 1
  17.          sql update 2
  18. ....................... Modd 2,3,4,7,8,9,10,
  19.     modd 11
  20.          sql update 11
  21.    end select
  22. end if
  23. sdt = sdt + 1 (also tried) sdt = dateadd("d",1,sdt)
  24. loop
  25.  
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
Jan 2 '08 #4

100+
P: 365
Any ideas?

Thanks
Jan 3 '08 #5

100+
P: 365
does anyone have any ideas?
Jan 4 '08 #6

100+
P: 365
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 & "'
Jan 6 '08 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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 (#)
Jan 8 '08 #8

100+
P: 365
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
Jan 8 '08 #9

P: 6
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)
Jan 8 '08 #10

100+
P: 365
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
Jan 8 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.