A
Arvi Laanemets
Hi
I have a table
Timetable:Minute, Field1, Field2, ...
In Minute field are stored Datetime values like 01.02.2007 00:01, 01.02.2007
00:02, etc, i.e. all minutes from some time interval. I need to calculate
other fields for this table, based on some query from several other tables
(there is no direct relation between Timetable and other tables). I'm trying
to do this in a procedure using ADODB recordsets.
There are 2 DateTime fields returned by query in similar format as in
Timetable table - Start and End. I need to count records in query recordset,
for which Minute value for current record in Timetable falls between Start
and end.
.....
Set cnCurrent = CurrentProject.Connection
Set rsTimetable = New ADODB.Recordset
rsTimetable .Open "Timetable", cnCurrent, adOpenStatic,
adLockOptimistic, adCmdTable
.....
strSQL = "SELECT ... , TableA.Start, TableA.End, ... ORDER BY
TableA.Start"
Set rsQuery = New ADODB.Recordset
rsQuery.Open strSQL, cnCurrent, adOpenStatic, adLockOptimistic
.....
rsTimetable .MoveFirst
rsQuery.MoveFirst
CurrMinute = rsTimetable .Fields("Minute")
.....
strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd\.mm\.yyyy\
hh:nn") & "'# AND [End] > #'" & Format(CurrMinute, "dd\.mm\.yyyy\ hh:nn") &
"'#"
.....
Do Until rsAjatabel.EOF
....
CntAll = 0
rsQuery.Find strCriteria
*** It's the previous row here where the program is getting an error***
If Not rsQuery.EOF Then
CntAll = CntAll + 1
Do Until rsQuery.EOF
rsQuery.Find strCriteria, 1
CntAll = CntAll + 1
Loop
End If
.....
rsQuery.MoveFirst
rsAjatabel.MoveNext
Loop
.....
The returned error is: "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another."
For sure the first search mustn't find anything, as there is no entry on
query table with Start <= 01.02.2007 00:00 And End > 01.02.2007 00:00
(really there are a lot of such rows in Timetable). I wanted the code to
process nect row in Timetable there.
Probably the problem is in DateTime values format? (I remember there was
something about using US formats somewhere.) Simply having CurrMinute
without using Format function doesn't do, because Access drops time part by
default. Has somebody an idea about this?
Thanks in advance!
I have a table
Timetable:Minute, Field1, Field2, ...
In Minute field are stored Datetime values like 01.02.2007 00:01, 01.02.2007
00:02, etc, i.e. all minutes from some time interval. I need to calculate
other fields for this table, based on some query from several other tables
(there is no direct relation between Timetable and other tables). I'm trying
to do this in a procedure using ADODB recordsets.
There are 2 DateTime fields returned by query in similar format as in
Timetable table - Start and End. I need to count records in query recordset,
for which Minute value for current record in Timetable falls between Start
and end.
.....
Set cnCurrent = CurrentProject.Connection
Set rsTimetable = New ADODB.Recordset
rsTimetable .Open "Timetable", cnCurrent, adOpenStatic,
adLockOptimistic, adCmdTable
.....
strSQL = "SELECT ... , TableA.Start, TableA.End, ... ORDER BY
TableA.Start"
Set rsQuery = New ADODB.Recordset
rsQuery.Open strSQL, cnCurrent, adOpenStatic, adLockOptimistic
.....
rsTimetable .MoveFirst
rsQuery.MoveFirst
CurrMinute = rsTimetable .Fields("Minute")
.....
strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd\.mm\.yyyy\
hh:nn") & "'# AND [End] > #'" & Format(CurrMinute, "dd\.mm\.yyyy\ hh:nn") &
"'#"
.....
Do Until rsAjatabel.EOF
....
CntAll = 0
rsQuery.Find strCriteria
*** It's the previous row here where the program is getting an error***
If Not rsQuery.EOF Then
CntAll = CntAll + 1
Do Until rsQuery.EOF
rsQuery.Find strCriteria, 1
CntAll = CntAll + 1
Loop
End If
.....
rsQuery.MoveFirst
rsAjatabel.MoveNext
Loop
.....
The returned error is: "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another."
For sure the first search mustn't find anything, as there is no entry on
query table with Start <= 01.02.2007 00:00 And End > 01.02.2007 00:00
(really there are a lot of such rows in Timetable). I wanted the code to
process nect row in Timetable there.
Probably the problem is in DateTime values format? (I remember there was
something about using US formats somewhere.) Simply having CurrMinute
without using Format function doesn't do, because Access drops time part by
default. Has somebody an idea about this?
Thanks in advance!