[AccessD] Date issues in Access

Stuart McLachlan stuart at lexacorp.com.pg
Thu Sep 18 23:05:17 CDT 2003


On 19 Sep 2003 at 13:43, connie.kamrowski at agric.nsw.go wrote:

>strSQL & ">=#" & datDay2 & "# And "
.....
> Set rst = db.OpenRecordset(strSQL)

Ah-ha!   You're using it in SQL.

 SQL assumes  US style dates where they are ambiguous.
Help actually says "You must use English (United States) date formats in 
SQL statements in Visual Basic." but as you've discovered, if it is not a 
valid US style date (such as 13/9/03),  it seems to work OK in most cases 
- but it is certainly not reliable.

Two possibilitues, either of which will work:

strSQL & ">=Datevalue('" & datDay2 & "') And "
(will interpret the date based on your Regional Settings)

or

strSQL & ">=#" & format$(datDay2,"mm/dd/yy") & "# And "






-- 
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg



More information about the AccessD mailing list