[AccessD] Un-American Date Filter

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




More information about the AccessD mailing list