Hi,
Am close to tearing my hair out on this one...
I am using MichealRed's deltadays function to establish the order in which 3 depts reviewed a document after the Originator released it, the release dates and thus deltadays is held in tbl_DDays.
I then need to establish deltadays for each dept in the order they reviewed the document and I am attempting this by opening a recordset and using the known days from Originator release to establish the correct order using nested If statements, then calculate a new deltadays from one dept to the next. The code below is only looking at the Technical Dept ([T]).
If I only use the first If statement it goes through without complaint and gives the correct figures. As soon as I bring in the second If statement (ie Elseif...) I get the "External Name not defined" error even though there is no new fields being referred to. "!L" or "!M" are highlighted as the culprits.
TDays(k) is an array variable collecting the deltadays for the Technical dept for each of the documents, eventually this will be totalling the total deltadays across all drafts of the document.
Any help most gratefully received!
Ken.
Set rst = db.OpenRecordset("SELECT tbl_DDays.[Code Number], " & _
"tbl_DDays.[Draft1 Originator Date], " & _
"tbl_DDays.[Draft1 Technical Date], " & _
"tbl_DDays.[Draft1 Medical Date], " & _
"tbl_DDays.[Draft1 Legal Date], tbl_DDays.[T], " & _
"tbl_DDays.[M], " & _
"tbl_DDays.[L] FROM tbl_DDays;"
rst.MoveLast
i = rst.RecordCount
rst.MoveFirst
ReDim Tdays(i)
k = 0
While Not rst.EOF
On Error Resume Next
Do Until k > i
k = k + 1
'Technical had document after Originator
If rst!T < rst!L Then
If rst!T < rst!M Then Tdays(k) = rst!T
'Technical had document after Legal
ElseIf rst!L < rst!T Then
If rst!T > rst!M Then
If rst!M < rst!L Then _
Tdays(k) = DeltaDays([Draft1 Legal Date], _
[Draft1 Technical Date])
'Technical had document after Medical
ElseIf rst!L < rst!T Then
If rst!T > rst!M Then
If rst!L < rst!M Then _
Tdays(k) = DeltaDays([Draft1 Medical Date], _
[Draft1 Technical Date])
End If
Debug.Print Tdays(k)
rst.MoveNext
Loop
Wend
Am close to tearing my hair out on this one...
I am using MichealRed's deltadays function to establish the order in which 3 depts reviewed a document after the Originator released it, the release dates and thus deltadays is held in tbl_DDays.
I then need to establish deltadays for each dept in the order they reviewed the document and I am attempting this by opening a recordset and using the known days from Originator release to establish the correct order using nested If statements, then calculate a new deltadays from one dept to the next. The code below is only looking at the Technical Dept ([T]).
If I only use the first If statement it goes through without complaint and gives the correct figures. As soon as I bring in the second If statement (ie Elseif...) I get the "External Name not defined" error even though there is no new fields being referred to. "!L" or "!M" are highlighted as the culprits.
TDays(k) is an array variable collecting the deltadays for the Technical dept for each of the documents, eventually this will be totalling the total deltadays across all drafts of the document.
Any help most gratefully received!
Ken.
Set rst = db.OpenRecordset("SELECT tbl_DDays.[Code Number], " & _
"tbl_DDays.[Draft1 Originator Date], " & _
"tbl_DDays.[Draft1 Technical Date], " & _
"tbl_DDays.[Draft1 Medical Date], " & _
"tbl_DDays.[Draft1 Legal Date], tbl_DDays.[T], " & _
"tbl_DDays.[M], " & _
"tbl_DDays.[L] FROM tbl_DDays;"
rst.MoveLast
i = rst.RecordCount
rst.MoveFirst
ReDim Tdays(i)
k = 0
While Not rst.EOF
On Error Resume Next
Do Until k > i
k = k + 1
'Technical had document after Originator
If rst!T < rst!L Then
If rst!T < rst!M Then Tdays(k) = rst!T
'Technical had document after Legal
ElseIf rst!L < rst!T Then
If rst!T > rst!M Then
If rst!M < rst!L Then _
Tdays(k) = DeltaDays([Draft1 Legal Date], _
[Draft1 Technical Date])
'Technical had document after Medical
ElseIf rst!L < rst!T Then
If rst!T > rst!M Then
If rst!L < rst!M Then _
Tdays(k) = DeltaDays([Draft1 Medical Date], _
[Draft1 Technical Date])
End If
Debug.Print Tdays(k)
rst.MoveNext
Loop
Wend