Jim Lawrence
accessd at shaw.ca
Thu Mar 31 23:54:58 CDT 2011
Actually, our government (federal and provincial) uses yyyymmdd which sorts as a string, a number or as a date without any translation. The other date standard they use is dd Mmm yyyy; ie. 01 May 2011 so there is never any confusion between month and day...and again everything is in sequential order. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee Sent: Thursday, March 31, 2011 4:08 PM To: Access Developers discussion and problem solving Subject: Re: [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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com