HELP!!!! comparing dates ina variable

[dantomi]

I'm stumped. This has got to be easy, so why does it seem so hard?
How do you accomplish the comparison of the date in a variable (TempDate)
to the date in the field [Holiday] in a the table GDLSHolidays? Could it
have something to do with the focus of the recordset? I have two
recordsets (tables) one is rst the other is rHol. Both are in the current
database. rst is the table SalesOrderPending and rHol is the table
GDLSHolidays.

The line in bold Italics is where the error occurs.

Do you have a way to do this in a Macro or Query? It has to be in a loop
that can be set a run time.

Dim strCriteria As String
strCriteria = "[Holiday] = TempDate"
Set rHol = dbs.OpenRecordset("GDLSHolidays", dbOpenDynaset)
rHol.FindFirst strCriteria
If rHol.NoMatch = False Then
TempDate = rHol!NextBusinessDay
End If

This looks just like the example in the Access Help feature, except they
have a text string where I have TempDate.

Here is the entire module:

Function Calc_Ship_Date()

'loop for calculating required delivery date
'all calculations based on the date of the order with the priority code
Dim TempDate As String
Dim FirstDateStep As String

'iShip is the number of non-holiday weekdays to add to the order date.
'x, y and z are the number of iterations in a particular loop
Dim iShip, x, y, z As Integer

Dim dbs As Database
Dim rst As Recordset
Dim rHol As Recordset


'Find out how many records are in SalesOrderPending to loop for each record
y = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SalesOrderPending", dbOpenDynaset)
With rst
.MoveFirst
.MoveLast
y = .RecordCount

'set the do loop for updating all records in the table
For x = 1 To y

Set rst = dbs.OpenRecordset("SalesOrderPending", dbOpenDynaset)

'set the value of TempDate to the order date in the first record
TempDate = rst!RLDate

'find out if order date is a weekend. If so, set TempDate to the next
Monday.
FirstDateStep = WeekDay(TempDate)
'If result is 1 the date is a Sunday and we will add 1
'if 7 it is a Saturday and we will 'add 2 days to get to the next Monday
'otherwise OK
If FirstDateStep = 1 Then TempDate = DateAdd("d", 1, TempDate)
If FirstDateStep = 7 Then TempDate = DateAdd("d", 2, TempDate)

--THIS IS WHERE THE ERROR OCCURS--

'find out if TempDate is a GDLS holiday.
'If so, set TempDate to the next business day.
Dim strCriteria As String
strCriteria = "[Holiday] = TempDate"
Set rHol = dbs.OpenRecordset("GDLSHolidays", dbOpenDynaset)
'Search for a matching record with TempDate and a holiday
rHol.FindFirst strCriteria
If rHol.NoMatch = False Then
TempDate = rHol!NextBusinessDay '
End If

'We now know TempDate is a non-holiday weekday.

'the following adds the required number of days to TempDate
iShip = 0

'find out from the priority code how many business days we have to add
'for the first record and sets the value of iShip to an integer
If rst!Priority < 4 Then
iShip = 2 'add two days
ElseIf rst!Priority < 8 Then
iShip = 5 'add five days
Else
iShip = 12
End If

'using iShip, add non-holiday work days
For z = 1 To iShip
'add one week day to TempDate
TempDate = DateAdd(&quot;w&quot;, 1, TempDate)

'see if we landed on a holiday.
'If so set TempDate to the next business day

strCriteria = &quot;[Holiday] = TempDate&quot;
Set rHol = dbs.OpenRecordset(&quot;GDLSHolidays&quot;, dbOpenDynaset)
'Search for a matching record with TempDate and a holiday
rHol.FindFirst strCriteria
If rHol.NoMatch = False Then
TempDate = rHol!NextBusinessDay '
End If

Next

'Update the table with the computed date
.Edit
!RequiredShipDate = TempDate
.Update

'go to the second record in SalesOrderPending and repeat the process

Next

End With

End Function

 

[allanon]

Hmmmm... as you said the example is using a string. What you are trying to do with yours is compare [holiday] to a string.... TempDate ... THIS WILL NOT WORK.


Try something like this...


strCriteria = &quot;[Holiday] = #&quot; & TempDate & &quot;#&quot;

Now TempDate is a variable. I am not entirely sure about the &quot;#&quot;. In most date comparisons they are needed.

Give it a try.