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!

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Regardless of what your regional settings may be, you cannot use dd/mm/yyyy
in SQL statements: you must use mm/dd/yyyy, or an unambiguous format such as
yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat 01.02.2007 as 2 Jan,
2007 in queries.

Take a look at Allen Browne's "International Dates in Access" at
http://www.allenbrowne.com/ser-36.html, or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". (You can
download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Arvi Laanemets" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>

Hi

Somehow I don't get it to work

I tried variants
strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn") &
"'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn") & "'#"
strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm") &
"'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm") & "'#"
strCriteria = "[Start] <= " & CDbl(CurrMinute) & " AND [End] > " &
CDbl(CurrMinute)
, and all of them returned same error!



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> Regardless of what your regional settings may be, you cannot use
> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
> 01.02.2007 as 2 Jan, 2007 in queries.
>
> Take a look at Allen Browne's "International Dates in Access" at
> http://www.allenbrowne.com/ser-36.html, or what I had in my September,
> 2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
> (You can download the column, and sample database, for free from
> http://www.accessmvp.com/DJSteele/SmartAccess.html)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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!
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>
>

It would appear you read neither what I said below, nor either of the two
articles I recommended.

"Regardless of what your regional settings may be, you cannot use dd/mm/yyyy
in SQL statements: you must use mm/dd/yyyy, or an unambiguous
format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
01.02.2007 as 2 Jan, 2007 in queries."

Try:

strCriteria = "[Start] <= " & Format(CurrMinute, "\#mm\/dd\/mm hh:nn\#")
&
" AND [End] > " & Format(CurrMinute, "\#mm\/dd\/yyyy hh:nn\#")

or

strCriteria = "[Start] <= " & Format(CurrMinute, "\#yyyy\-mm\-dd
hh:nn\#") &
" AND [End] > " & Format(CurrMinute, "\#yyyy\-mm\-dd hh:nn\#")


Note that not only did I add the # delimiters to the Format function call,
but I also removed the single quotes that you had included in error.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Arvi Laanemets" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> Somehow I don't get it to work
>
> I tried variants
> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn")
> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn") & "'#"
> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm")
> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm") & "'#"
> strCriteria = "[Start] <= " & CDbl(CurrMinute) & " AND [End] > " &
> CDbl(CurrMinute)
> , and all of them returned same error!
>
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:(E-Mail Removed)...
>> Regardless of what your regional settings may be, you cannot use
>> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
>> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
>> 01.02.2007 as 2 Jan, 2007 in queries.
>>
>> Take a look at Allen Browne's "International Dates in Access" at
>> http://www.allenbrowne.com/ser-36.html, or what I had in my September,
>> 2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
>> (You can download the column, and sample database, for free from
>> http://www.accessmvp.com/DJSteele/SmartAccess.html)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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!
>>>
>>> --
>>> Arvi Laanemets
>>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>>
>>>
>>
>>
>
>

Oops!

I read your posting at home, and went for an hour to my workplace to check
it out. There I used copy-paste from your posting to modify the format - it
looks like I copied from wrong place.


Arvi Laanemets



"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:Oymt$(E-Mail Removed)...
> It would appear you read neither what I said below, nor either of the two
> articles I recommended.
>
> "Regardless of what your regional settings may be, you cannot use
> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
> 01.02.2007 as 2 Jan, 2007 in queries."
>
> Try:
>
> strCriteria = "[Start] <= " & Format(CurrMinute, "\#mm\/dd\/mm hh:nn\#")
> &
> " AND [End] > " & Format(CurrMinute, "\#mm\/dd\/yyyy hh:nn\#")
>
> or
>
> strCriteria = "[Start] <= " & Format(CurrMinute, "\#yyyy\-mm\-dd
> hh:nn\#") &
> " AND [End] > " & Format(CurrMinute, "\#yyyy\-mm\-dd hh:nn\#")
>
>
> Note that not only did I add the # delimiters to the Format function call,
> but I also removed the single quotes that you had included in error.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi
>>
>> Somehow I don't get it to work
>>
>> I tried variants
>> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn")
>> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn") & "'#"
>> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm")
>> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm") & "'#"
>> strCriteria = "[Start] <= " & CDbl(CurrMinute) & " AND [End] > " &
>> CDbl(CurrMinute)
>> , and all of them returned same error!
>>
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>>
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:(E-Mail Removed)...
>>> Regardless of what your regional settings may be, you cannot use
>>> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
>>> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
>>> 01.02.2007 as 2 Jan, 2007 in queries.
>>>
>>> Take a look at Allen Browne's "International Dates in Access" at
>>> http://www.allenbrowne.com/ser-36.html, or what I had in my September,
>>> 2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
>>> (You can download the column, and sample database, for free from
>>> http://www.accessmvp.com/DJSteele/SmartAccess.html)
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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!
>>>>
>>>> --
>>>> Arvi Laanemets
>>>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi

Tried it again, but without any success! Here are a couple of criteria
syntaxes I tried lately.

strCriteria = "[Start] <= #'" & Format(CurrMinute, "mm\/dd\/yyyy
hh\:nn") & "'# AND [End] > #'" & Format(CurrMinute, "mm\/dd\/yyyy hh\:nn") &
"'#"
strCriteria = "[Start] <= " & DateValue(Format(CurrMinute,
"mm\/dd\/yyyy")) + TimeValue(Format(CurrMinute, "hh:nn")) & " AND [End] > "
& DateValue(Format(CurrMinute, "mm\/dd\/yyyy")) +
TimeValue(Format(CurrMinute, "hh:nn"))

I also wrapped a formula to calculate field End in SQL (it was a calculated
field) into CVDate() function, but without any sucess too.

I can use same fields in Watch window, and same comparisions for current
record (rsQuery.Fields("Start") <= CurrMinute; rsQuery.Fields("End") >
CurrMinute) are working properly (are returnung True or False). It looks
like it is Find method that fails somehow.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:Oymt$(E-Mail Removed)...
> It would appear you read neither what I said below, nor either of the two
> articles I recommended.
>
> "Regardless of what your regional settings may be, you cannot use
> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
> 01.02.2007 as 2 Jan, 2007 in queries."
>
> Try:
>
> strCriteria = "[Start] <= " & Format(CurrMinute, "\#mm\/dd\/mm hh:nn\#")
> &
> " AND [End] > " & Format(CurrMinute, "\#mm\/dd\/yyyy hh:nn\#")
>
> or
>
> strCriteria = "[Start] <= " & Format(CurrMinute, "\#yyyy\-mm\-dd
> hh:nn\#") &
> " AND [End] > " & Format(CurrMinute, "\#yyyy\-mm\-dd hh:nn\#")
>
>
> Note that not only did I add the # delimiters to the Format function call,
> but I also removed the single quotes that you had included in error.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi
>>
>> Somehow I don't get it to work
>>
>> I tried variants
>> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn")
>> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:nn") & "'#"
>> strCriteria = "[Start] <= #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm")
>> & "'# AND [End] > #'" & Format(CurrMinute, "dd/mm/yyyy hh:mm") & "'#"
>> strCriteria = "[Start] <= " & CDbl(CurrMinute) & " AND [End] > " &
>> CDbl(CurrMinute)
>> , and all of them returned same error!
>>
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>>
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:(E-Mail Removed)...
>>> Regardless of what your regional settings may be, you cannot use
>>> dd/mm/yyyy in SQL statements: you must use mm/dd/yyyy, or an unambiguous
>>> format such as yyyy-mm-dd or dd mmm yyyy. Access will ALWAYS treat
>>> 01.02.2007 as 2 Jan, 2007 in queries.
>>>
>>> Take a look at Allen Browne's "International Dates in Access" at
>>> http://www.allenbrowne.com/ser-36.html, or what I had in my September,
>>> 2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
>>> (You can download the column, and sample database, for free from
>>> http://www.accessmvp.com/DJSteele/SmartAccess.html)
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Arvi Laanemets" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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!
>>>>
>>>> --
>>>> Arvi Laanemets
>>>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>>>
>>>>
>>>
>>>
>>
>>
>
>