Format double field as 'Long Time'

[devQuestion]

I have recently upgraded my Access 2003 application to Access 2010. I am running into a problem when formatting a textbox to 'Long Time' that is linked to a Double type field. The error that I get is 'The value you entered isn't valid for this field. You may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.'

The textbox is linked to a field (double data type) and displays as the time in long format (4:06:17 PM). When I try to change to 4:07:17 PM I get the error. I tried removing the 'PM' and the colons but the error still occurs. The only way that I can get it to work is if I use a double value (0.735405092592593). In Access 2003 the time value would display properly, allow me to change the time and then save the time as a double to the database.

Does anyone have an idea of what has changed or what I now need to do in Access 2010? Thanks for your help.

 

[MarkK]

I don't know what changed, but why store a date/time value as a double? The solution I recommend going forward is store your date/time data in a date/time field.

 

[devQuestion]

The application is very old (18+ yrs) and that is how it was designed. All dates and times are stored separately and it would be a lot of work to change it. I'm hoping that there is an easier way to address this.

 

[MarkK]

Well, you can hide your bound textbox and show an unbound textbox, and then handle the AfterUpdate event of the unbound version. If there is a valid date, update the table.

Sub txtUnboundDate_AfterUpdate()
   if isdate(me.txtUnboundDate) then me.txtHiddenBoundDoubleDate = CDbl(me.txtUnboundDate)
end sub

. . . and of course you need to load the saved date for each new record . . .

sub form_current()
   me.txtUnboundDate = null
   if not me.newrecord then me.txtUnboundDate = CDate(Me.txtHiddenBoundDoubleDate)
end sub

Does that make sense? But this is an ugly work-around, and it degrades the value of your system IMO.