[AccessD] Un-American Date Filter

Darryl Collins Darryl.Collins at iag.com.au
Thu Mar 31 18:00:48 CDT 2011


_______________________________________________________________________________________

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.
_______________________________________________________________________________________




More information about the AccessD mailing list