Hey all,
I'm working with a database that contains null date values. I'm trying to create a fairly large query by passing an SQL string that receives its input from a form. I already have a large part of the script up and running but I'm unable to get past the problem described below:
My goal is the following to make a copy of the DB with GetRows(). I then want to look specifically at rows 18 and 35 (I have verified that these are the rows that I want - i.e. the 2d array from getrows is zero-based). Row 18 is the 'original target date' (OTD) and 35 is the 'revised target date' (RTD). If the RTD = null, then I want to use the original target date (which won't be null). I want to take either RTD or OTD (if RTD is null) and subtract from the Date() function. If the result is negative, then that particular record is "Overdue". Below is the code:
'This is the form that I was talking about
If [Form_report-selector].Combo126.Value <> "*" Then
'If the value <> "*" then it is Overdue
Set MyQueryDef = Nothing
Set rs = Nothing
MySQL = "SELECT * FROM capa"
Set MyQueryDef = CurrentDb.CreateQueryDef("", MySQL)
Set rs = MyQueryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges, dbOptimistic)
sample = rs.GetRows(nbOfRows)
For i = 0 To nbOfRows - 1
rtd = sample(35, i)
otd = sample(18, i)
If rtd = Null Then ' This IF doesn't seem to work
Datedif = otd - Date ' OTD won't be null
Else:
Datedif = rtd - Date ' It hangs here with error 94: Invalid Use of Null
End If
If Datedif < 0 Then
id = sample(0, i)
capa_id_str = id + "'" + id + "',"
together = "capa.capa_id in (" + Left(id, Len(id) - 1) + ") AND "
isoverdue = "capa.original_target_date - Date() < 0 "
Else
End If
Next
rs.Close
Set rs = Nothing
MyQueryDef.Close
Set MyQueryDef = Nothing
End If
My question is: why won't it skip the first If so that it won't hang? Any help would be appreciated.