[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