[AccessD] Un-American Date Filter

Actually I think it is the Japanese that got it right with YYYYMMDD

On Thu, Mar 31, 2011 at 4:00 PM, Darryl Collins
<Darryl.Collins at iag.com.au>wrote:

>
>
> _______________________________________________________________________________________
>
> Note: This e-mail is subject to the disclaimer contained at the bottom of
> this message.
>
> _______________________________________________________________________________________
>
>
>
> Hi Rocky,
>
> To avoid these sort of issues I use dateserial on each and every date I
> process in SQL / VBA to make sure they line up correctly.  Also from memory
> the VBE treats all dates passed in VBA as American format as default,
> regardless of the PC's regional setting. Coming from the land of
> "dd-mm-yyyy" as standard it is an issue I need to deal with a lot.
>
> Now, if only you pesky Americans got with the program, used a proper date
> format and just swallowed your pride and admit the French were right and
> adopt the (far superior) metric system it would make life for the rest of
> the planet much easier.  ;)
>
> cheers
> Darryl.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Friday, 1 April 2011 5:35 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Un-American Date Filter
>
> Doug:
>
> When I change the code to
>
> "DueDate >= #" & CDate(Format(Me.txtGEDueDate, "dd/m/yyyy")) & "#"
>
> And
>
> "DueDate <= #" & CDate(Format(Me.txtLEDueDate, "dd/mm/yyyy")) & "#"
>
> the sql statement still comes out:
>
> INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate, QuantityDue
> ) SELECT tblDemand.PartNumber, tblDemand.OrderNumber, tblDemand.DueDate,
> tblDemand.QuantityDue FROM tblDemand  WHERE DueDate >= #31/03/2011# AND
> DueDate <= #07/04/2011#
>
> And still no date filtering.
>
> R
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
> Sent: Thursday, March 31, 2011 11:21 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Un-American Date Filter
>
> Hi Rocky:
>
> I've dealt with this in the past by forcing the date format to mm/dd/yy in
> the SQL, something like:
>
> WHERE DueDate >= Cdate(format(ClientInputDateFrom,"mm/dd/yy"))....etc
>
> Doug
>
> On Thu, Mar 31, 2011 at 10:55 AM, Rocky Smolin <rockysmolin at bchacc.com>
> wrote:
> > Dear List:
> >
> > I create a temp table using a SQL statement I construct in code to
> > incorporate various filter - one of which is a date filter.
> >
> > Works well here but when I send it to the user in Bahrain who's
> > regional setting is (I assume from the screen shot he sent) English
> > U.K. where the date format is dd/mm/yyyy it fails.  I set my regional
> > settings on my box to U.K. and sure enough it fails - returns all records
> with no date filtering.
> >
> > Here's the SQL statement that creates the table:
> >
> > INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate,
> > QuantityDue
> > ) SELECT tblDemand.PartNumber, tblDemand.OrderNumber,
> > tblDemand.DueDate, tblDemand.QuantityDue FROM tblDemand  WHERE DueDate
> > >= #31/03/2011# AND DueDate <= #07/04/2011#
> >
> > which is supposed to filter out any record in tblDemand where the Due
> > Date is outside of the range March 31 to April 7.  But it don't.
> >
> > When I look in tblDemand, the dates are displayed properly as dd/mm/yyyy.
> >
> > Why doesn't this work?  Should I be using some kind of different
> > syntax for this filter?
> >
> > MTIA
> >
> >
> > Rocky Smolin
> >
> > Beach Access Software
> >
> > 858-259-4334
> >
> > Skype: rocky.smolin
> >
> > www.e-z-mrp.com <http://www.e-z-mrp.com/>
> >
> > www.bchacc.com <http://www.bchacc.com/>
> >
> >
> >
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> _______________________________________________________________________________________
>
> The information transmitted in this message and its attachments (if any) is
> intended
> only for the person or entity to which it is addressed.
> The message may contain confidential and/or privileged material. Any
> review,
> retransmission, dissemination or other use of, or taking of any action in
> reliance
> upon this information, by persons or entities other than the intended
> recipient is
> prohibited.
>
> If you have received this in error, please contact the sender and delete
> this e-mail
> and associated material from any computer.
>
> The intended recipient of this e-mail may only use, reproduce, disclose or
> distribute
> the information contained in this e-mail and any attached files, with the
> permission
> of the sender.
>
> This message has been scanned for viruses.
>
> _______________________________________________________________________________________
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>