[AccessD] Dates As Criteria

Jim Lawrence accessd at shaw.ca
Wed Feb 14 12:31:03 CST 2007


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/





More information about the AccessD mailing list