Rocky Smolin
rockysmolin at bchacc.com
Thu Mar 31 18:44:48 CDT 2011
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