[AccessD] Filtering with International Dates

Hi Rocky

You are a clever man not to be fooled!

There really is no reason to convert dates to numerals. If you can use the date value, use it as is, if not - as often when you build SQL strings - convert it to a proper string expression. The proven method is to use Format and the ISO format:

Format(somedatetime, "yyyy\/mm\/dd hh\:nn\:ss")
For JET SQL this must be wrapped in #..#, for SQL Server it is single quotes '..'
You may get away with using the US mm/dd/yyyy format but it will fail for ADO and FindFirst, thus you can just as well make it a habit to use the ISO format which now is the preferred for SQL Server as well.

If you need to cut off a time part to obtain the date part only, a safe and the fastest method is Fix:

  Fix(somedatetime)

because it:

 - works correctly for dates prior to 1899-12-30
 - returns data type date
 - always rounds off
 - is native to SQL

The use of CDbl or CLng is not very useful. CDbl just returns the underlying data type (Double) of data type Date so you should CDate instead, and CLng will round PM time parts incorrectly, and they both fail for a clean string input like "5/3/2005" which CDate does not.
If you need to convert strings to date and time, use CDate for time expressions or to include a time part with a date, or DateValue for dates where the time part should be excluded.

When to use date value and when a string expression for a date value is quite simple. When a function is used in SQL, it should return a date value; when you build a SQL string it must be a string expression. Thus, as mentioned by Stuart, this will work because CDate and DateValue (and TimeValue) understands a string formatted as the local settings of Windows:

  "where [datefield] = Datevalue('" & txtDate & "')"

as will this:

  "where [datefield] = #" & Format(DateValue(txtDate), "yyyy\/mm\/dd") & "#"

which, as Format - when using a date format - will try to read the value to format as a date, can be reduced to:

  "where [datefield] = #" & Format(txtDate, "yyyy\/mm\/dd") & "#"

/gustav


>>> rockysmolin at bchacc.com 23-09-2011 05:17 >>>
What happens if you use CDbl on a date string with no time?

R