Using MSDE for Backend to Access 2000? HOT!!!

[DougP]

we have MSDE Version 8.00.760(SP3) running on NT5.2(3790)

The 'DateTime' Data type has a problem when we try to enter just a 'Time' like
8:00 for example
Get Error Date Overflow (0)
It wants the Date as well such as '1899-01-01 08:00:00'
The dates we enter get a default time added to the end
like '2004-01-01 00:00:00'
But times don't
Using ODBC to connect
Any ideas how to solve this?
I have SQL Server Personal Edition and it does not have this problem on MSDE.

Is there a way to format the text box to send '1899-01-01 08:00:00' to the database but only show 08:00AM on the form?
I also know I can write code
Insert into blah blah blah Convert( DATETime ....
but that a lot of trouble






DougP, MCP, A+

 

[SiJP]

FormatDateTime(Date[,NamedFormat])

For Example:

"INSERT INTO tblYourTable(mytimefield) VALUES('" & FormatDateTime(Time, 4) & "')"

This will insert the current time into the field...

vbGeneralDate (0)
Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.

vbLongDate (1)
Display a date using the long date format specified in your computer's regional settings.

vbShortDate (2)
Display a date using the short date format specified in your computer's regional settings.

vbLongTime (3)
Display a time using the time format specified in your computer's regional settings.

vbShortTime (4)
Display a time using the 24-hour format (hh:mm).


------------------------
Hit any User to continue

 

[DougP]

Does this go into the Format field of the text box?

DougP, MCP, A+

 

[DougP]

I guess you missed that this is a TEXT BOX
I want to format the text box

I don't want to write a SQL string?

DougP, MCP, A+

 

[SiJP]

Doug, sorry chap - teach me to start reading things properly!!

I'm assuming that you've a bound text field to your SQL table? As a suggestion...

1) Create an input mask for the field to accept the time format you wish.

2) Write a Before_Update event to take the value entered into the field and format appropriately (e.g. take 08:00 and append the '1899-01-01 " xx:xx ":00'" to either side of the string, then use the CDate function within the event to modify the data type.

I believe this should work.. if not let me know and I'll play around with this when I'm back at work tomorrow and post some code.

Si


------------------------
Hit any User to continue