Re: us date format

Douglas J. Steele wrote:

>"Marshall Barton" wrote
>> Douglas J. Steele wrote:
>>
>>>"Marshall Barton" 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.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Open Enterpirse Manager with Button
    ... Thanks Douglas, that worked perfectly. ... Billy Rogers ... Currently Using SQL Server 2000, ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.formscoding)
  • Re: SQL Synatx Incorrect
    ... Douglas J. Steele wrote: ... Could it be the [UsageDate] = Date. ... unlike, say, SQL Server, Access does not allow SQL ...
    (microsoft.public.access.formscoding)
  • Re: call function module from query - having much trouble
    ... I apologize about that, I am new to Access being linked to SQL server and ... Douglas J. Steele wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: VS 2005 Help, SQL 2005 Help,and .NET 2.0 Help is very UNhelpful and poorly tested.....completely
    ... very unpredictable about what you get (when installing VWD Express on one ... With the above said, overall, I truly like the new look of the Help system. ... > of the CLR in SQL Server when you do a help for something, ... > so you can get hired by them as a "yes" man programmer. ...
    (microsoft.public.dotnet.general)
  • Re: ORDER BY in VIEW not working
    ... As an object-oriented programmer, I expect to be able to encapsulate ... But a relational database is not in Kansas, ... has statistics, and the statistics are unique to the table, because the ... Again, while you don't care about performance, most users of SQL Server do, ...
    (comp.databases.ms-sqlserver)