[AccessD] Filtering with International Dates

Hi Rocky

Yes, that will work. It will return #09/23/2011# for today.

I've never noticed any difference between Format and Format$ (and all the other old BASIC string handling functions with or without a trailing $).
SomeFunction$ is claimed to always return a string, but I've never found, say, Trim to return anything else than a string. Maybe it is just to be compatible with old QBasic or similar.

/gustav


>>> rockysmolin at bchacc.com 23-09-2011 14:35 >>>
Gustav:

Dang - it's like taking a class.  :)

I found this on the web as you suggest:

Format$(Forms!frmPOReport!txtGEPromisedDate, "\#mm\/dd\/yyyy\#") 

(Don't know why the $ after Format but it compiles OK)
 
And it seems to work.  But I have to comb through my manufacturing app now -
which is a very date driven app, lots of reports where I give the user the
option to filter by date - and change those occurrences to something which
will be as bulletproof as possible.

It looks like that's the best approach in your opinion?

Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, September 23, 2011 3:11 AM
To: accessd at databaseadvisors.com 
Subject: Re: [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