Rocky Smolin
rockysmolin at bchacc.com
Thu Mar 31 13:49:31 CDT 2011
Shamil: I think that's what Doug was trying to tell me? Anyway, IT'S WORKING!!! I changed the code to CDate(Format(Me.txtGEDueDate, "mm/dd/yyyy")). Oddly, the SQL still shows the date as dd/mm/yyyy. Thank you one and all. Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Thursday, March 31, 2011 11:40 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Un-American Date Filter Rocky -- SQL "knows" American mm/dd/yyyy dates only - your constructed in code SQL expression should be: INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate, QuantityDue ) SELECT tblDemand.PartNumber, tblDemand.OrderNumber, tblDemand.DueDate, tblDemand.QuantityDue FROM tblDemand WHERE DueDate >= #03/31/2011# AND DueDate <= #04/07/2011# -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: 31 ????? 2011 ?. 21:56 To: 'Access Developers discussion and problem solving' Subject: [AccessD] Un-American Date Filter 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