Re: date and time syntax

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ken Snell (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 06/20/04 

Date: Sat, 19 Jun 2004 20:06:44 -0400

You can add a date to a time by delimiting the date value with # and then
adding to the time that was entered:

Me.somefield = #1/1/1990# + Me.ApptTime 

-- 
        Ken Snell
<MS ACCESS MVP>
"smk23" <smk23@discussions.microsoft.com> wrote in message
news:5BC7DF40-C4F0-4860-A286-33B6B4E621C2@microsoft.com...
> Greetings all!
> I have a time field that holds appointment time. Typically the user will
only enter the time. My BE is SQL server 2k and the data type is
smalldatetime for this column.
> The FE is Access 2002. The date default Access enters if there is no date
specified is 12/31/1899 which is out of the date range for smalldatetime and
an error is generated.
>
> One way to solve this is to add a couple of days on an AfterUpdate event
when the user enters a time. This has problems because the time field will
not sort correctly if there are a variety of dates preceding the time.
Rather than allow Access to insert a default date, it seems that the best
way to resolve this is to enter my own date.
>
> I tried the following syntax ideas:
> Me.somefield= CDate("1/1/1990 " & Me.ApptTime)
>    or
> Me.somefield = Me.ApptDate & Me.ApptTime
>
> I get an "wrong data type" error with both.
> How have others addressed this issue? Apparently MS Access 2003 has
corrected this, but I don't have the ability to update right now.
>
> -- 
> sam

Relevant Pages

  • SQL Server Job step failing.
    ... but fails as part of the Job in '2005 (on the SQL Server 2005 d/b). ... smalldatetime data type resulted in an out-of-range smalldatetime value. ... Any thoughts as to why the '2005 Job will not execute this step, ...
    (microsoft.public.sqlserver.programming)
  • Re: Btrive time fields in Crystal Report dont display properly
    ... use the Crystal 8.5 merge module in Installshield developer. ... Data Type 4 is definitely a standard TIME field. ... >> This app was first developed in 1996, and we started with Smithware. ...
    (comp.databases.btrieve)
  • Stored procedure: column or parameter error
    ... 'Cannot specify a column width on data type smalldatetime.' ... I'm using SQL Server & I've check the data type in the db & it's same ... as smalldatetime. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Column width
    ... 'Cannot specify a column width on data type smalldatetime.' ... I'm using SQL Server & I've check the data type in the db & it's same ... as smalldatetime. ...
    (microsoft.public.dotnet.general)
  • Re: Date column wont update
    ... FYI, if you wanted to keep the columns defined as smalldatetime, the SP input parameters should also be smalldatetime and the ... The proper mapping of the MSSQL datetime data type to ADO data type is adDBTimeStamp. ...
    (microsoft.public.vb.database.ado)