date and time syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
Greetings all!
I have a time field that holds appointment time. Typically the user will
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
corrected this, but I don't have the ability to update right now.
 
Ken:
Thanks so much. I should have known that syntax, but sometimes crossing back and forth between SQL and Access I get my wires crossed.
I greatly appreciate your time on this and many other posts.
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

smk23 said:
Ken:
Thanks so much. I should have known that syntax, but sometimes crossing
Click to expand...
back and forth between SQL and Access I get my wires crossed.
 
Back
Top