ADO find Date

[Guest]

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Guest]

I guess could use:

rst.Find "[DataDate] = <#12/01/04# and => #12/30/04#"

but I prefer to use between.

Such is life!

 

[Douglas J. Steele]

Is the DataDate field a date field, or is it text? Does using a 4 digit year
make any difference?

 

[Douglas J. Steele]

That definitely will not work. First of all, you'd need to repeat the field
name:

rst.Find "[DataDate] = <#12/01/04# and [DateDate] => #12/30/04#"

Secondly, it's not possible for a date to be before Dec. 1st AND after Dec.
30th!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I guess could use:

rst.Find "[DataDate] = <#12/01/04# and => #12/30/04#"

but I prefer to use between.

Such is life!

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Brendan Reynolds]

The use of '=<' instead of '<=' and '=>' instead of '>=' looked strange to
me, so I did a little testing. While '=<' was accepted in a simple
expression in the Immediate window, it failed when I tried it in a query,
and failed again when I tried it with the Find method of an ADO recordset.
The error message in the latter case was "Arguments are of the wrong type,
are out of acceptable range, or are in conflict with one another", as in the
original post in this thread. Looks like it's a fairly generic error message
returned when ADO doesn't understand the argument.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

That definitely will not work. First of all, you'd need to repeat the
field
name:

rst.Find "[DataDate] = <#12/01/04# and [DateDate] => #12/30/04#"

Secondly, it's not possible for a date to be before Dec. 1st AND after
Dec.
30th!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I guess could use:

rst.Find "[DataDate] = <#12/01/04# and => #12/30/04#"

but I prefer to use between.

Such is life!

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Douglas J. Steele]

Jeez, I didn't even notice that DataDay had the wrong operators! That still
doesn't explain getting the message when using Between though, does it?

 

[Brendan Reynolds]

No, assuming that the field is a date field, as you've already asked, I
can't see anything wrong with the original code using BETWEEN.

Um, hang on though - this is probably a long shot, but are we sure we're
dealing with a Jet database here? Would it make a difference if the back-end
was SQL Server or MSDE?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Jeez, I didn't even notice that DataDay had the wrong operators! That
still
doesn't explain getting the message when using Between though, does it?

 

[Guest]

Thank you both for responding.

Yes, it is a Data/Time field in an Access Database. I am opening the ADO
recordset from Excel using:

dbfullname = "C:\Data\YOY Analysis\YOY.mdb"

strSQL = "YOYData"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname & ";"
'Create DB connection
Set rst = CreateObject("ADODB.Recordset")

At this point in code I a new record to the record set. Then based on the
date of that new record, I need to search for the record in the corisponding
month from the previous year. I'd like to use between for the dates but it
does not seem to work and neighther does the >= and <= method. Right now in
a pinch I am closing the recordset and reopening it based on my criteria.

Is the DataDate field a date field, or is it text? Does using a 4 digit year
make any difference?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Douglas J. Steele]

How do you populate the recordset?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thank you both for responding.

Yes, it is a Data/Time field in an Access Database. I am opening the ADO
recordset from Excel using:

dbfullname = "C:\Data\YOY Analysis\YOY.mdb"

strSQL = "YOYData"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname & ";"
'Create DB connection
Set rst = CreateObject("ADODB.Recordset")

At this point in code I a new record to the record set. Then based on the
date of that new record, I need to search for the record in the corisponding
month from the previous year. I'd like to use between for the dates but it
does not seem to work and neighther does the >= and <= method. Right now in
a pinch I am closing the recordset and reopening it based on my criteria.

Is the DataDate field a date field, or is it text? Does using a 4 digit year
make any difference?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Guest]

With rst
Set .ActiveConnection = cnn
.Source = strSQL
.Open , , 3, 3
end with

How do you populate the recordset?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thank you both for responding.

Yes, it is a Data/Time field in an Access Database. I am opening the ADO
recordset from Excel using:

dbfullname = "C:\Data\YOY Analysis\YOY.mdb"

strSQL = "YOYData"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname & ";"
'Create DB connection
Set rst = CreateObject("ADODB.Recordset")

At this point in code I a new record to the record set. Then based on the
date of that new record, I need to search for the record in the corisponding
month from the previous year. I'd like to use between for the dates but it
does not seem to work and neighther does the >= and <= method. Right now in
a pinch I am closing the recordset and reopening it based on my criteria.

Is the DataDate field a date field, or is it text? Does using a 4 digit year
make any difference?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[onedaywhen]

From the help for Find method:

Only a single-column name may be specified in criteria ...
(for example, "start_date > #7/22/97#").

I'd recommend the Filter property, which supports multiple columns e.g.


rst.Filter = "DataDate >= '2004-12-07' AND DataDate < '2004-12-09'"
Jamie.

--

 

[Brendan Reynolds]

It seems that the ADO Find method just doesn't understand 'BETWEEN'.
According to the ADO help file ...

<quote>
The comparison operator in Criteria may be ">" (greater than), "<" (less
than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal),
"<>" (not equal), or "like" (pattern matching).
</quote>

As Jamie has pointed out elsewhere in this thread, the ADO Find method also
accepts only a single column name. Although it's not specifically stated in
the help file, I think that also means only one instance of each column
name, in other words, when you try to use DataDate >= something And DataDate
<= something else, I think the two references to the column DataDate count
as two column names as far as the Find method is concerned.

In short, I think you'll have to use the Filter method as Jamie suggested,
or use the criteria in the Open method as you're doing now, or use DAO
instead of ADO - you can use BETWEEN or multiple columns with the DAO
FindNext method.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank you both for responding.

Yes, it is a Data/Time field in an Access Database. I am opening the ADO
recordset from Excel using:

dbfullname = "C:\Data\YOY Analysis\YOY.mdb"

strSQL = "YOYData"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname &
";"
'Create DB connection
Set rst = CreateObject("ADODB.Recordset")

At this point in code I a new record to the record set. Then based on the
date of that new record, I need to search for the record in the
corisponding
month from the previous year. I'd like to use between for the dates but
it
does not seem to work and neighther does the >= and <= method. Right now
in
a pinch I am closing the recordset and reopening it based on my criteria.

Is the DataDate field a date field, or is it text? Does using a 4 digit
year
make any difference?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out
of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks

 

[Guest]

Thanks Jamie and Brendan,

I think I'll stick with my current method for now and when I get the time
I'll play with the filter.

It seems that the ADO Find method just doesn't understand 'BETWEEN'.
According to the ADO help file ...

<quote>
The comparison operator in Criteria may be ">" (greater than), "<" (less
than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal),
"<>" (not equal), or "like" (pattern matching).
</quote>

As Jamie has pointed out elsewhere in this thread, the ADO Find method also
accepts only a single column name. Although it's not specifically stated in
the help file, I think that also means only one instance of each column
name, in other words, when you try to use DataDate >= something And DataDate
<= something else, I think the two references to the column DataDate count
as two column names as far as the Find method is concerned.

In short, I think you'll have to use the Filter method as Jamie suggested,
or use the criteria in the Open method as you're doing now, or use DAO
instead of ADO - you can use BETWEEN or multiple columns with the DAO
FindNext method.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank you both for responding.

Yes, it is a Data/Time field in an Access Database. I am opening the ADO
recordset from Excel using:

dbfullname = "C:\Data\YOY Analysis\YOY.mdb"

strSQL = "YOYData"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbfullname &
";"
'Create DB connection
Set rst = CreateObject("ADODB.Recordset")

At this point in code I a new record to the record set. Then based on the
date of that new record, I need to search for the record in the
corisponding
month from the previous year. I'd like to use between for the dates but
it
does not seem to work and neighther does the >= and <= method. Right now
in
a pinch I am closing the recordset and reopening it based on my criteria.

Is the DataDate field a date field, or is it text? Does using a 4 digit
year
make any difference?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hello,

I'm using an ADO record set. It will find the records when I use:

rst.Find "[DataDate] = " & date

but when I use the following:

rst.Find "[DataDate] between #12/07/04# and #12/09/04#"

I get an error message saying "Arguments are of the wrong type, are out
of
acceptable range, or are in conflict with one another".

Any idea what is wrong with my code?

Thanks