[AccessD] Dates As Criteria

JWColby jwcolby at colbyconsulting.com
Wed Feb 14 13:15:12 CST 2007


Yep, this solution needs to give a different alias to ArrestDT or you will
get circular references. 

SELECT Format([ArrestDT],"Short Date") as ArrestDTNew,
tblArrests.ArrestLocation FROM tblArrests WHERE ArrestDT Between
   [Forms]![frmReports]![StartDate] And
   [Forms]![frmReports]![EndDate];


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, February 14, 2007 1:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Dates As Criteria

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




More information about the AccessD mailing list