Re: ADO find Date

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: DataDay (DataDay_at_discussions.microsoft.com)
Date: 12/09/04 

Date: Thu, 9 Dec 2004 11:20:00 -0800

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.

"Brendan Reynolds" wrote:

> 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.
>
>
> "DataDay" <DataDay@discussions.microsoft.com> wrote in message
> news:62C066C1-9B59-4D86-9165-68E23B77145F@microsoft.com...
> > 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.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> 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
> >> http://I.Am/DougSteele
> >> (No private e-mails, please)
> >>
> >>
> >> "DataDay" <DataDay@discussions.microsoft.com> wrote in message
> >> news:D36613AB-3ADB-4F86-8A26-5237BA79D108@microsoft.com...
> >> > 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
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: ADO/ALTER TABLE problem
    ... a DDL query executed via an ADO connection. ... "Brian Morris" wrote in message ... >> GlobalSign digital certificate is a forgery and should be deleted without ... >>> Based on your LENexample I definitely have to use VARCHAR. ...
    (microsoft.public.data.ado)
  • RE: Data Entry Form
    ... Microsoft ActiveX Data Objects 2.6 Library or below (AKA ADO) ... Microsoft DAO 3.6 Object Library ... In the help file, look for/type in the search tab for ...
    (microsoft.public.excel.programming)
  • Re: Heavy Mail Merge Application
    ... Word requires at least one data record in order to provide a list of ... What I generally tend to do is use ADO (or whatever connection method is most ... Then repeat the read, filter and merge. ... This reply is posted in the Newsgroup; please post any follow question or reply ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access and Delphi?
    ... On client-side cursor (default in VCL ADO) find and filter are implemented ... > server applications that connect to a SQL server, ...
    (borland.public.delphi.database.ado)
  • Finding the better performance filtered table vs record fetch
    ... I am accessing a metadata table using two methods. ... Two - grab only the record needed. ... Recordsets use an ado client side ... faster than the filter. ...
    (microsoft.public.sqlserver.connect)