Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Mar 31 16:58:24 CDT 2011
Hi Rocky -- <<< IT'S WORKING!!! >>> Great! <<< Oddly, the SQL still shows the date as dd/mm/yyyy. >>> Do you mean QBE (MS Access Query Designer/Query By Example) grid's criteria values presented in dd/mm/yyyy format while SQL expression string has date values in American mm/dd/yyyy format? I haven't seen your code but from your description using Format(Me.txtGEDueDate, "mm/dd/yyyy") should be enough, while using CDate(Format(Me.txtGEDueDate, "mm/dd/yyyy")) could result in wrong SQL where criteria - CDate(...) is "guessing" (sometimes wrongly) what Date value should be while converting it from its string representation - have a look/try to type in Immediate window: ?Format(CDate(Format(DateSerial(2011,4,1), "dd/mm/yyyy")),"dd-mmm-yyyy") 01-apr-2011 ?Format(CDate(Format(DateSerial(2011,4,1), "mm/dd/yyyy")), "dd-mmm-yyyy") 04-jan-2011 ?Format(CDate(Format(DateSerial(2011,4,21), "dd/mm/yyyy")),"dd-mmm-yyyy") 21-apr-2011 ?Format(CDate(Format(DateSerial(2011,4,21), "mm/dd/yyyy")), "dd-mmm-yyyy") 21-apr-20111 Of course date string used in SQL expression in American format should be enclosed in a pair of '#' symbols - #04/01/2011# .... Thank you. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: 31 ????? 2011 ?. 22:50 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Un-American Date Filter 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com