PDA

View Full Version : VB date error and transitioning to Case



Swaink
03-03-2010, 09:59 AM
Hey im having trouble with this bit of code. Ideally it is supposed to print the info to an excel sheet for any days that the program missed. I get trapped in an infinite loop with my Do until loop and i think that transitioning this to a case statement might make things easier.

the code is as follows:

'Prints previous dates if program not ran. Excluding weekends
Do Until (DateAdd("d", 1, NewDate) = Date) 'Loops until current date
'Determines if date was missed and if it is a weekend day
If (Weekday(SentFailedLog.Fields("Date")) = (Today - 1)) Then
isYesterday = True

End If
If (Weekday(SentFailedLog.Fields("Date")) = 1) Then
isWeekend = True
ElseIf (Weekday(SentFailedLog.Fields("Date")) = 7) Then
isWeekend = True
End If
'If program missed running a day excel sheet is filled in w/ missed date
If (isYesterday = False And isWeekend = False) Then
'NewDate is date missed (Calculated by adding 1 to last date)
NewDate = DateAdd("d", 1, SentFailedLog.Fields("Date"))
With SentFailedLog
.AddNew
.Fields("Date") = NewDate
.Fields("Cycle") = -35
.Fields("Compact&Repair") = "No"
.Fields("File Size") = File_Size_MB
.Fields("F8") = "PROGRAM NOT RAN"
.Update
End With
SentFailedLog.MoveLast
End If
Loop

any help with pseudo code would be awesome!
Thanks

Edit: VBA tags added to code. You can format your code for the forum by selecting it when posting and hitting the VBA button.

Bob Phillips
03-03-2010, 10:14 AM
Case is euivqlent to an If statement, not a loop of any kind.

It is somewhat difficult to see what is happening, it seems to be updating a database, but we are only seeing a bit.

Why do you do

NewDate = DateAdd("d", 1, SentFailedLog.Fields("Date"))

not

NewDate = DateAdd("d", 1, NewDate)

Swaink
03-03-2010, 10:27 AM
the loop and the transition to a case are two separate issues. I use the NewDate = DateAdd("d", 1, SentFailedLog.Fields("Date")) because the code updates an excel sheet that we use as a log. so if a day is missed you wouldnt want to use the current date you would want to use the next date after the last one in the sheet.

The code posted should fill in any dates between when the program was last run up to the current date.