Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Mar 31 17:41:57 CDT 2011
Rocky --
<<<CDate(...) is "guessing" (sometimes wrongly)>>>
I should have written that CDate(...) uses MS Windows system locale date
format to convert String date representation to its Date value.
And doing so it could produce results not expected/intended to be used by a
developer as in this case:
?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
when system locale date format was 'dd/mm/yyyy' and so first result was
correct while the second... was correct also ... but didn't conform
developer's intention as they got January 4th, 2011 instead of April 1st,
2011...
And here system locale date format is also 'dd/mm/yyyy' and both results are
"correct":
?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-2011
I quoted word correct because both results produce 21-April-2011 which is
equal to the source date DateSerial(2011,4,21) but CDate(...) should
better(?) produce runtime error for CDate("4/21/2011") on systems with
"dd/mm/yyyy" system locale date format...
(and on your system with 'mm/dd/yyyy' system locale date format you'll get
CDate('21/4/2011') - 21-April-2011, which is confusing if you don't know
what happens "behind the curtains"
On your system with 'mm/dd/yyyy' date format set in the system locale you'll
get opposite to my first sample results:
?Format(CDate(Format(DateSerial(2011,4,1), "dd/mm/yyyy")),"dd-mmm-yyyy")
01-jan-2011
?Format(CDate(Format(DateSerial(2011,4,1), "mm/dd/yyyy")), "dd-mmm-yyyy")
04-apr-2011
And in MS Access Query Designer (QBE Grid) dates are presented using system
locale date/time format as well as in MS Access form's textboxes if you use
"Short Date" format....
Hope that helps.
Thank you.
--
Shamil
-----Original Message-----
From: Shamil Salakhetdinov [mailto:shamil at smsconsulting.spb.ru]
Sent: 1 ?????? 2011 ?. 1:58
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Un-American Date Filter
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