[AccessD] Un-American Date Filter

Stuart McLachlan stuart at lexacorp.com.pg
Thu Mar 31 19:29:41 CDT 2011


It's not just Japanese, it's the internationally accepted standard in ISO 8601.

Unfortunately the UScentric developers of Access and SQL Server ignore the international 
standard and use an internationally ambiguous format instead. 

-- 
Stuart

On 1 Apr 2011 at 11:08, Darryl Collins wrote:

> hahahaha!  - Nah, just having a rant about the perils of the crazy
> imperial measurement that the US still uses.  I actually agree with
> David (and the Japanese).  "YYYYMMDD" makes the most sense of all,
> well to me at least.
> 
> 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 10:45 AM To: 'Access Developers
> discussion and problem solving' Subject: Re: [AccessD] Un-American
> Date Filter
> 
> Metric dates...hmm... 10 days per week, then?
> 
> Rocky
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
> Collins Sent: Thursday, March 31, 2011 4:01 PM To: Access Developers
> discussion and problem solving Subject: Re: [AccessD] Un-American Date
> Filter
> 
> 
> ______________________________________________________________________
> ______ ___________
> 
> 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
> 
> 
> -- 
> 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
> 







More information about the AccessD mailing list