[AccessD] Un-American Date Filter

Rocky Smolin rockysmolin at bchacc.com
Thu Mar 31 18:44:48 CDT 2011


Metric dates...hmm... 10 days per week, then?

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, March 31, 2011 4:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Un-American Date Filter


____________________________________________________________________________
___________

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.
____________________________________________________________________________
___________

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list