[AccessD] Un-American Date Filter

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Thu Mar 31 19:51:53 CDT 2011


Rocky --

You don't need DateValue.

Are your source textboxes bound to Date type tables' fields? 
If yes - then just Format( .., "mm/dd/yyyy".) 'quoted' into pair of '#'
would be enough.
If your source textboxes do have String type values and that values are
system locale format dependent then you can use:

mySql = MySql & ..... &   "#" & Format(CDate(txtMyDate.Text),"mm/dd/yyyy") &
"#"

or

mySql = MySql & ..... &   "#" & Format(Cate(txtMyDate.Text),"mm/dd/yyyy") &
"#"

or

mySql = MySql & ..... &   "#" &
Format(DateValue(txtMyDate.Text),"mm/dd/yyyy") & "#"

or

again just 

mySql = MySql & ..... &   "#" & Format(txtMyDate.Text,"mm/dd/yyyy") & "#"

all the above will work OK

but

mySql = MySql & ..... &   "#" & CDate(Format(txtMyDate.Text,"mm/dd/yyyy")) &
"#"  or
mySql = MySql & ..... &   "#" & CVDate(Format(txtMyDate.Text,"mm/dd/yyyy"))
& "#"   or
mySql = MySql & ..... &   "#" &
DateValue(Format(txtMyDate.Text,"mm/dd/yyyy")) & "#"

wouldn't be correct as SQL "knows" only American date format and CDate(...),
CVDate(....), DateValue(...) will return date formatted using system locale
date format...

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: 1 ?????? 2011 ?. 3:03
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Un-American Date Filter

Shamil:

When I create those SQL strings I usually load them into a text box on the
form before I execute them so I can see what it looks like and copy it out
to a query if I need to run it.  Stuart suggest DateValue.  Better or worse
than Format?

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 2:58 PM
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

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