[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