Go to new record on a new day

[David W. Fenton]

Why? When inside the quotes the value will be evaluated by the
Expression Service as an actual DateTime, not as a string that
looks like a date. Delimiters should not be necessary at all.
Just as they are not necessary when you use Date() or Now() in the
criteria of a query.

Because you are explicitly doing what you are depending on the
expression service to do implicitly. This particular example is
trivial, but as a matter of course, I always resolve function
returns *before* passing them in a filter or SQL string. I just
think it's good practice for all cases, particularly because in a
lot of cases (such as db.OpenRecordset) it's required.

 

[Douglas J. Steele]

It means you don't have to worry about formats, nor about implicit
coercion of a string type (which is what Format() returns) into a
date type.


I always resolve a reference to a function once, and try to pass
string values only as filters, i.e., no functions. One of the
reasons for that is that when opening a recordset, not all functions
can be used, so it's important to resolve them before passing the
SQL to the OpenRecordset. I just see this as good practice overall.

I'm well aware of issues with date formats. I just can't envision how you'd
use DateSerial in this case.

Can you please give an example?

 

[Rick Brandt]

Because you are explicitly doing what you are depending on the
expression service to do implicitly. This particular example is
trivial, but as a matter of course, I always resolve function
returns *before* passing them in a filter or SQL string. I just
think it's good practice for all cases, particularly because in a
lot of cases (such as db.OpenRecordset) it's required.

I have always assumed that in such a case the ES would pass the actual
unadulterated date value (the double numeric value) and that would eliminate
any sort of formatting issue.

 

[David W. Fenton]

I'm well aware of issues with date formats. I just can't envision
how you'd use DateSerial in this case.

Can you please give an example?

Take the data provided and break it into its component parts, then
pass those parts to DateSerial(). Back when I first encountered this
problem (in 1998, with an app written in NYC that was starting to be
used in London) that was the definitive answer given. Yes,
unamiguous date formats work, too, but they involve type coercion
through string values, and I just don't see the point in that.

On the other hand, yes, there are 3 function calls to get the
component parts for passing to DateSerial(), but they can't go
wrong, whereas it's too easy to get the wrong format string.

 

[David W. Fenton]

I have always assumed that in such a case the ES would pass the
actual unadulterated date value (the double numeric value) and
that would eliminate any sort of formatting issue.

Er, what?

Format() returns a string, and that string is then coerced into a
date value.

If you're using DateSerial() with the Day(), Month() and Year()
functions, you can pass each of those your date value and then those
will be passed as integers to DateSerial(), which returns a variant
of date subtype (so it can be easily coerced to a string, I guess).

OK, I'll back down a bit, since I was thinking it returned a value
of Date type. Since it's a variant, there has to be some coercion
involved, and you're just trading coercion from variant subtype date
to date for coercion from string to date -- probably not much
difference there in the end, so it probably just comes down to
style. I prefer doing things as explicitly as possible, and
formatting a date to a string seems an indirect method (and too
prone to error if you don't properly type the format string).

 

[RoyVidar]

I would change that to:

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate =#" & Date() & "#"

With my settings, that would barf with RT 3075, cause the *string*
passed to the evaluation services, would look like this

txtDate =#26.09.2007#

which Jet isn't very happy with.

With UK settings, it would cause anomalities where day < 13

When we're using dynamic SQL, as for instance in the WhereCondition
of some of the DoCmd methods, it's important that what we pass to
the evaluation services, aren't dates, numbers, text etc. It's a
*string*.

This string will contain the names of the fields, some operators
and literals representing the values we wish to pass. If the
literals are in the correct format, have the correct delimiters,
etc, Jet will understand, evaluate (and implicitly coerce?)
correctly, and perform. Else, the result is anomalities or RT 3075.

Evaluation services can happily deal with some functions, as for
instance the Date() function, so the *string*

"txtDate = Date()"

as WhereCondition is quite OK, as the OP suggests.

If we use the format function on a valid date when concatenating
dynamic SQL, the date is explicitly converted to a string literal
using the format specified (one would probably use either ISO 8601
or mm\/dd\/yyyy), which will make Jet able to understand the
*string* when passed, regardless of locale, because the string
literal concatenated into the SQL string, is in a unambiguous
format.

Note also that some regional settings specify comma as decimal
separator, which makes some tweaking necessary also for numbers
with decimals, when concatenating dynamic SQL. One might for
instance use the Str() function on such numbers.

You can pass "dates" to dynamic SQL through other means, too, for
instance by using Long or Double *) (here, in the latter case,
relying on a bit implicit coersion).

Say

CurrentDB.Execute "UPDATE myTable SET myDate = " & Str(CDbl(Now)) & _
" WHERE myID = 42", dbFailOnError

DoCmd.OpenForm "SomeForm", , , "myDate =" & Clng(Date)

But again, as far as I've understood, what Jet (or the expression
services) receives, evaluates and finally performs, is not dates,
text, or numbers, but a *string* with literals representing the
values we pass. Here, the first string will get a properly
formatted decimal number (i e, the Str function replaces the comma
I would get as decimalseparator with my settings, with dot), the
latter coercion is implicit, but I usually allow that for long.

Thise concatenated strings are then passed to Jet for evaluation
and execution.

*) 0 date of SQL server and Jet is off by two days, which means the
above will provide anomalities when executed on ADO OLE DB vs ODBC
with SQL server

SELECT Cast(0 As DateTime) -> 01/01/1900
Debug.Print Format(0, "mm\/dd\/yyyy") -> 12/30/1899
)

 

[David W. Fenton]

With my settings, that would barf with RT 3075, cause the *string*
passed to the evaluation services, would look like this

txtDate =#26.09.2007#

which Jet isn't very happy with.

With UK settings, it would cause anomalities where day < 13

Oh. I didn't know we were dealing with dates outside of the US.

I strongly dislike the idea of passing functions for evaluation by
the expression service in this context, but I see no simpler
alternative here.

 

[Rick Brandt]

Er, what?

Format() returns a string, and that string is then coerced into a
date value.

Where is Format() involved? I was referencing this statement...

DoCmd.OpenForm "NorthWeldDailyfrm", , , "txtDate = Date()"

In my mind that should be exactly equivalent to having a query in the query
designer and using Date() as a criteria. The value should be evaluated
directly as a date, not as any type of formatted string that has to be coerced
to a date.