[AccessD] Dates As Criteria

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/
> 
>