[AccessD] Dates As Criteria

Thanks Jim...but that would cause a Circular Reference...I think??? And if I 
named ArrestDT something else...we are back to where I started...not 
returning the results I wanted.  This does disturb me a little be cause I've 
used FORMAT(DT_Field,"Short Date") a thousands of times...and never had this 
issue.  Using FIX does seem to 'fix' the problem...lol...I'm still 
confused...but ok.

Thanks,

Mark


>From: Jim Lawrence <accessd at shaw.ca>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Dates As Criteria
>Date: Wed, 14 Feb 2007 10:31:03 -0800
>
>Hi Mark:
>
>The process can be great sped up by just formatting the results like:
>
>SELECT Format([ArrestDT],"Short Date") as ArrestDT,
>tblArrests.ArrestLocation
>FROM tblArrests
>WHERE ArrestDT Between
>    [Forms]![frmReports]![StartDate] And
>    [Forms]![frmReports]![EndDate];
>
>That is if I am understanding what you are asking.
>
>Jim
>
>-----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 7: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
>
>_________________________________________________________________
>Dont 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

_________________________________________________________________
Get in the mood for Valentine's Day. View photos, recipes and more on your 
Live.com page. 
http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701