Gary Kjos
garykjos at gmail.com
Fri Feb 16 18:11:55 CST 2007
That will only work if you don't have times in your Oracle based
dates. The end date is OK but the start date will get you all the
records that have any time other than 00:00:00 for the start date. I
put the time into the form that the user uses to enter the date range
and tell them to use 00:00:00am for the start date and 11:59:59pm for
time in the end date or 23:59:59 for those who prefer the 24 hour
clock.
Some of our Oracle Dates have times and some don't. Even sometimes the
same field will sometimes have times in some records and other records
won't, it depends on the program that updated that field in the Oracle
Application. By always selecting from the beginning time of the
beginning day to the ending time of the last day we get all the
records.
GK
On 2/16/07, O'Connor, Patricia (OTDA)
<Patricia.O'Connor at otda.state.ny.us> wrote:
> I stopped using BETWEEN quite awhile ago because of problems I had run
> into using Access as a front end to Oracle. Even in passthru queries and
> straight PlSql queries I use the date before and the date after. This
> way I am guaranteed to get all records with the correct date even if the
> "time" is 00:00:00.
>
> SO if you wanted between March 15, 2004 and July 10, 2004 I would write
>
> SELECT tblArrests.ArrestDT, tblArrests.ArrestLocation
> FROM tblArrests
> WHERE ArrestDT > '14-MAR-2004'
> And ArrestDt < '11-JUL-2004'
>
> So in your form you could have hidden fields with the day added and
> subtracted or
>
> SELECT tblArrests.ArrestDT, tblArrests.ArrestLocation
> FROM tblArrests
> WHERE ArrestDT > DateAdd("D",-1,[Forms]![frmReports]![StartDate] )
> And ArrestDt < DateAdd("D",1,[Forms]![frmReports]![EndDate] )
>
> Or something similar
>
> **************************************************
> * Patricia O'Connor
> * Associate Computer Programmer Analyst
> * OTDA - BDMA
> * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> * (w) mailto:aa1160 at nysemail.state.ny.us
> **************************************************
>
>
> >
> --------------------------------------------------------
> This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
>
>
> -----Original Message-----
>
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Mark A Matte
> > Sent: Wednesday, February 14, 2007 10:46 AM
> > To: accessd at databaseadvisors.com
> > Subject: Re: [AccessD] Dates As Criteria
> >
> > I set the parameters...no change...and the reason I I use the
> > format function is that the field ArrestDT is stored as a
> > general date...with time.
> > So I format to get just the short date.
> >
> >
> > >From: "Gustav Brock" <Gustav at cactus.dk>
> > >Reply-To: Access Developers discussion and problem
> > >solving<accessd at databaseadvisors.com>
> > >To: <accessd at databaseadvisors.com>
> > >Subject: Re: [AccessD] Dates As Criteria
> > >Date: Wed, 14 Feb 2007 16:30:03 +0100
> > >
> > >Hi Mark
> > >
> > >First, I would follow the advice from Arthur.
> > >If not possible, then try this:
> > >
> > >PARAMETERS
> > > [Forms]![frmReports]![StartDate] DateTime,
> > > [Forms]![frmReports]![EndDate] DateTime;
> > >
> > >SELECT tblArrests.ArrestDT, tblArrests.ArrestLocation
> > >FROM tblArrests
> > >WHERE ArrestDT Between
> > > [Forms]![frmReports]![StartDate] And
> > > [Forms]![frmReports]![EndDate];
> > >
> > >/gustav
> > >
> > > >>> markamatte at hotmail.com 14-02-2007 16:09:43 >>>
> > >Here she is...This is making NO SENSE to me...at all???
> > >
> > >SELECT tblArrests.ArrestDT, tblArrests.ArrestLocation
> > >FROM tblArrests
> > >WHERE (((Format([ArrestDT],"Short Date")) Between
> > >[Forms]![frmReports]![StartDate] And
> > [Forms]![frmReports]![endDate]));
> > >
> > >
> > >
> > > >From: "Gustav Brock" <gustav at cactus.dk>
> > > >Reply-To: Access Developers discussion and problem
> > > >solving<accessd at databaseadvisors.com>
> > > >To: <accessd at databaseadvisors.com>
> > > >Subject: Re: [AccessD] Dates As Criteria
> > > >Date: Wed, 14 Feb 2007 00:13:12 +0100
> > > >
> > > >Hi Mark
> > > >
> > > >Show us your query, the SQL.
> > > >It probably has to do with a missing US format of the date values.
> > > >
> > > >/gustav
> > > >
> > > > >>> markamatte at hotmail.com 13-02-07 22:22 >>>
> > > >Hello All,
> > > >
> > > >In A2K I ahve a form that has 2 unbound date fields...have
> > input masks
> > >and
> > > >formats as SHORT DATE...I have a query that uses these
> > fields as criteria
> > >in
> > > >a BETWEEN statement. The field in the table is a general
> > date. I'm
> > >using 2
> > > >records to check...if I set the startdate and enddate on
> > the form to the
> > > >dates that are in the db...they return..but if I use ofther
> > >dates...nothing
> > > >comes back. Example:
> > > >
> > > >StartDate:2/6/2007
> > > >EndDate: 2/13/2007
> > > >
> > > >2 records:
> > > >DT=2/9/2007
> > > >DT=2/13/2007
> > > >
> > > >Using the above "between startdate and enddate" I return 1 record?
> > > >If I change the startdate to 2/9/2007 then I return both
> > > >IF I use 2/6 and2/17 as the start and end...I return NOTHING...
> > > >
> > > >Anyone...please help...I'm sure I've done something
> > silly...but this just
> > > >doesn't make sense???
> > > >
> > > >Thanks,
> > > >
> > > >Mark A. Matte
> > >
> > >
> > >--
> > >AccessD mailing list
> > >AccessD at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/accessd
> > >Website: http://www.databaseadvisors.com
> >
> > _________________________________________________________________
> > Don't miss your chance to WIN 10 hours of private jet travel
> > from Microsoft
> > Office Live
> > http://clk.atdmt.com/MRT/go/mcrssaub0540002499mrt/direct/01/
> >
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com