[AccessD] Filtering with International Dates

Format returns a Variant of type String.
Format$ returns an actual string.

Similarly with Left,Right,Mid,Trim etc

Theoretically, using the native types rather than variants should be more efficient. Wait a 
minute....

Yep - that's true in practice as well:

<code>
Option Compare Database
Option Explicit

Const gc_testcases As Long = 10000000

Function TestVar() As String
Dim str As String
Dim strResult As String
Dim t As Single
Dim x As Long
t = Timer
For x = 1 To gc_testcases
  str = Left("abcdef", 3)
Next
t = Timer - t
strResult = "Variant form took " & t & " seconds" & vbCrLf
t = Timer
For x = 1 To gc_testcases
  str = Left$("abcdef", 3)
Next
t = Timer - t
TestVar = strResult & "String form took " & t & " seconds"
End Function
</code>

The Variant form consistently takes 2.5 times as long as the String version.

-- 
Stuart

On 26 Sep 2011 at 10:54, Darryl Collins wrote:

> Yeah, that was my understanding of it.  The "$" is short hand for
> String.
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav
> Brock Sent: Friday, 23 September 2011 10:59 PM To:
> accessd at databaseadvisors.com Subject: Re: [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 
> 
> 
> -- 
> 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
>