DateAdd (1 Viewer)

[andisign]

I have some code that goes round in a loop. Outside the loop is an openrecordset statement, where all the dates need to be in US format. I then within the loop need to use the dateadd function to add one day onto a date everytime the loop runs. The date add function doesn't work because it gets its settings from Control panel which are set to UK. The settings in Control Panel must stay as UK format. Please help, I am going round and round in circles and keep getting really stupid dates back out of this loop.

Code below:

Dim Date2 As Date
Dim untildate As Date
Dim TDate As Date
Dim ConvertDate1 As String
Dim ConvertDate2 As Date
Dim ConvertDate3 As String
Dim ConvertDate4 As Date

TDate = Format(Date, "mm/dd/yyyy")
Date2 = Format(Me!Date2, "mm/dd/yyyy")

Set rst1 = CurrentDb.OpenRecordset("SELECT tblcost.ID, tblcost.Period_start, tblcost.Period_end, tblcost.Frequency From tblcost " & _
"WHERE (((tblcost.Period_start) < #" & TDate & "#) And ((tblcost.Period_end) >= #" & TDate & "# And (tblcost.Period_end) < #" & Date2 & "#) " & _
"And ((tblcost.Frequency) Is Not Null)) ORDER BY tblcost.Period_start " & _
"UNION SELECT tblcost.ID, tblcost.Period_start, tblcost.Period_end, tblcost.Frequency From tblcost WHERE (((tblcost.Period_start) >= #" & TDate & "#) " & _
"And ((tblcost.Period_end) <= #" & Date2 & "#) And ((tblcost.Frequency) Is Not Null)) ORDER BY tblcost.Period_start " & _
"UNION SELECT tblcost.ID, tblcost.Period_start, tblcost.Period_end, tblcost.Frequency From tblcost WHERE (((tblcost.Period_start) >= #" & TDate & "# " & _
"And (tblcost.period_start)<= #" & Date2 & "#) And ((tblcost.Period_end) > #" & Date2 & "#) And ((tblcost.Frequency) Is Not Null)) " & _
"ORDER BY tblcost.Period_start " & _
"UNION SELECT tblcost.ID, tblcost.Period_start, tblcost.Period_end, tblcost.Frequency From tblcost WHERE (((tblcost.Period_start) < #" & TDate & "#) " & _
"And ((tblcost.Period_end) > #" & Date2 & "#) And ((tblcost.Frequency) Is Not Null)) ORDER BY tblcost.Period_start;", dbOpenDynaset)

rst1total = 0
With rst1
.MoveFirst
End With
rst1check = False
Do
rst1Date = TDate
'MsgBox rst1Date
If Date2 < rst1!Period_end Then
untildate = Date2
Else
untildate = Format(rst1!Period_end, "mm/dd/yyyy")
End If
If IsNull(rst1!Frequency) = True Then
rst1frequency = ""
Else
rst1frequency = rst1!Frequency
End If

Do 'Until rst1Date = DateAdd("y", 1, untildate)
rst1Date = Format(rst1Date, "mm/dd/yyyy")
If (rst1Date >= TDate) And (rst1Date <= rst1!Period_end) Then
If rst1frequency <> "" And rst1frequency <> "other" Then
Dim dlookup1, dlookup2
dlookup1 = DLookup("[termdateid]", "tbldaysofterm", "[termdate] = #" & rst1Date & "#")
If IsNull(dlookup1) = False Then
dlookup2 = DLookup(rst1frequency, "tbldaysofterm", "[termdate] = #" & rst1Date & "#")
If dlookup2 = -1 Then

rst1Day = Format(rst1Date, "dddd")
rst13day = Format(rst1Date, "ddd")
rst1TransCost = DLookup("[" & rst1Day & "]", "tblcost", "[id] =" & rst1!ID)
rst1EscortCost = DLookup("[escort" & Format(rst1Date, "ddd") & "]", "tblcost", "[id] =" & rst1!ID)
rst1total = rst1total + rst1TransCost + rst1EscortCost
End If
End If
End If
End If

ConvertDate1 = Day(rst1Date) & "/" & Month(rst1Date) & "/" & Year(rst1Date)
ConvertDate2 = DateValue(ConvertDate1)
ConvertDate2 = DateAdd("d", 1, ConvertDate2)
ConvertDate3 = Day(ConvertDate2) & "/" & Month(ConvertDate2) & "/" & Year(ConvertDate2)
ConvertDate4 = DateValue(ConvertDate3)
rst1Date = ConvertDate4

Loop Until rst1Date >= untildate

Please help!

Andrea

 

[Rich]

Why do the dates need to be in the US format?

 

[andisign]

For the SQL statement in the openrecordset. It doesn't work with the UK format.

 

[Fizzio]

I'm not sure that the dateAdd problem is due to the regional settings as all it does is add a value to the date regardless of format.
Have you tried merging these lines
ConvertDate2 = DateValue(ConvertDate1)
ConvertDate2 = DateAdd("d", 1, ConvertDate2)

into something more like
ConvertDate2 = DateAdd("d", 1, DateValue(ConvertDate1))
but even then I'm not sure that is the answer.

I would consider throwing in watchpoints into your debug to ascertain the values of ConvertDate1 and ConvertDate2 if you are sure that this is the problem area.

 

[andisign]

I have tried using the watch points and the dates are just messed up the minute it adds a day on as it adds a month on instead, but you can't then say well add a month on because you want to go above 12 days. ARGHHHHHH!!! I HATE DATES IN ACCESS! But thanks for the suggestion! It is like you have to format it before adding the day on, but then it has to be formatted back which is what I was trying to do, but it still doesn't work when it goes round the loop a second time.

 

[Fizzio]

A couple more thoughts.

Have you tried removing this line as it seems to serve no real purpose in the loop.
rst1Date = Format(rst1Date, "mm/dd/yyyy")

Have you played around with table level formatting (I'm sure you already have though)

Also following on from Rich's question, do you really need to format the dates in US style? I have dates working fine in UK style with regional settings to UK in recordsets.

 

[Rich]

Although you have to use the US date format for the Where clause I would have thought you're better using the UK format to add a day, since you want the output to be in UK format.
I think you've made this more complicated than it needs to be, why not open the recordset first to see if it's producing the records you require, they should be returned in UK format.