Marshall Barton wrote in message
Douglas said:
Douglas J. Steele wrote:
Bob Wickham wrote:
I don't know the context where Steve recommended using CLng,
but the only reason I can think of is to drop the Time part
of a date value. Not to say Steve didn't have something
clever in mind, but I do not see how it can help in a
situation inolving the Default Value.
I believe what Steve may have been suggesting is that you can use:
Me![PaymentDate].DefaultValue = CLng(Me![PaymentDate])
rather than
Me![PaymentDate].DefaultValue = "#" & Format$(Me![PaymentDate],
"yyyy-mm-dd") & "#"
I never thought of that. It is clever, but it does rely on
Access to convert from a long to a date. While I don't
really have a problem with that, it is not in the slightest
portable to other contexts that don't provide the same
implicit conversions.
I don't really understand this comment, Marsh.
I don't see any reliance on converting from a long to a date. Dates
ARE numbers, after all. You can use the numeric equivalent of the
date anywhere. Today (22 Jan, 2006) is 38739 days since 30 Dec, 1899,
so CLng(Date) will give you 38739:
?CLng(Date)
38739
You can add, say, 31 to that to get 38970 and then format that as a
date, and you'll get 22 Feb, 2006:
?38739 + 31
38770
?Format(38770, "dd mmm yyyy")
22 Feb 2006
I don't feel it even relies on knowing that dates start at 30 Dec,
1899: 0 could represent any date, and this approach would still work
in Access.
Based on that thinking, I wouldn't trust it in SQL Server
SQL statements without seeing some definitive documentation
to that effect.
I don't disagree with this statement: I believe SQL Server uses a
different start date (so that 0 isn't 30 Dec, 1899, like it is in
Access and Excel), but I'm not certain about that. However, even if
your front-end is linked to SQL Server, there's no reason why you
can't use this "trick" to set the default value for a field. If
Access is doing translations when communicating with SQL Server,
it'll do the same translations in either case.
I agree that this will work as long as the conversions are
done in Access.
What I am questioning is a situation where the long value is
saved to a table and then processed by another system, e.g.
Excel and SQL Server. If/When the long values is then used
in that context, it could generate wrong dates or require
the programmer to be aware of the situation and to program
Access's zero date in the other system. As long as the
programmer is totally aware of the issues with dates, the
Long value dates would not be stored to tables and the issue
would not arise.
I would not have brought up this extrapolation of Bob's
situation, if Bob had not stated repeatedly that he expected
to have to upsize his application.
It's probably not very nice to have a go at a suggestion made by an NG
participant who hasn't participated here (yet) - but - their advice is
given in a completely different context, and in this context, it has
been mentioned a possible upsize to SQL server and questions about the
clng method when concatenating a dynamic SQL string.
For those who bother, and/or have a possibility of testing, here's a
small sample
Create a SQL server table (dbo.DateTest), with fields
ID, Int, Identity PK
myText, Varchar(50)
myDate, DateTime
Then try to insert a record with date the "clng way" both on the linked
table, and on a connection
dim cn as adodb.connection
set cn = new adodb.connection
cn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
' connection string from here (watch for linebreaks)
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer
' execute on connection to the SQL server
cn.execute "insert into dbo.DateTest (myText, myDate) " & _
"Values ('connection', " & clng(date) & ")"
' execute on "current db" -> linked table
currentproject.connection.execute "
"insert into dbo_DateTest (myText, myDate) " & _
"Values ('linked', " & clng(date) & ")"
on my setup, that gives two different dates (see below).
SQL server - select convert(datetime, 0) -> 1/1/1900
VBA format$(0, "m/d/yyyy") -> 12/30/1899
?date -> 1/22/2006
?format$(date, 0) -> 38739
select convert(datetime, 38739) -> 1/24/2006
so - at least on my setups, there's a two day difference between these
two samples, which makes me be blunt enought to say: be careful with
this approach if there's a possibility of
1 upsize to SQL server, and
2 executing action queries with date parameters on a separate connection
using dynamic SQL
Anyone else verify?
I think I will continue to recommend using the format function when
assigning date parameters to dynamic SQL strings. Btw - when converting
a date to a long, wouldn't it also create an implicit conversion/cast of
it to string when concatenating whith the dynamic SQL string?
I'm thinking that most problems I've seen when the format function has
been used, is when one has forgotten to escape the date separators (as
in for instance using "mm/dd/yyyy" in stead of "mm\/dd\/yyyy". The first
of these formats, would barf on my settings, but again "yyyy-mm-dd"
should work everywhere).
The ISO-8601 date format is supposed to work with most recent databases,
so I'll keep using it, should I work with date paramters in dynamic SQL
strings.
But why not use the Clng for GUI and pure/native Jet/Access stuff?
Speaking for myself, I think I'd get into troubles using different
methods for practically the same, I'd probably not remember when and
where to use what, but that's me ;-)
