Date + Time Problem

  • Thread starter Thread starter DEI
  • Start date Start date
D

DEI

I have a form with fields for entry of times in short time format. I would
like the user to enter only the 'short time' only, eg. '13:24', but then I
would like to actually store the data in the underlying table with the
complete date and time, eg. '7/24/2008 1:24 PM'.

I have code in the after update event that adds the date to the time:

Me.field1 = field1 + [Date]

However, the [Date] is actually a date entered in a field on another open
form that is an unbound control, in short date format. I sometimes get a
'type mismatch' error; the date entered in the unbound control is recognized
as text and not a date.

What is further problematic is that it does not always happen on all
workstations, i.e some users get the error and others do not.

Is there anything I can do to keep this from happenning?

Thanks in advance.

DEI
 
Suggestions:

1. Change the name of the text box on the other from to (say) txtDate.

2. If txtDate is unbound (not bound to a date/time field), set the Format
property of the text box to Short Date or similiar, so it won't accept
non-date values.

3. Make sure you don't assign a non-date value to txtDate (e.g.
programmatically, or by its default value, etc.)

4. In your code, make sure the other form is open, and that txtDate contains
a valid date.

5. You may also want to check that Field1 is actually a time value without a
date, and that txtDate is a date without a time component.

This kind of thing:

If Me.Field1 Between #00:00:00# And #23:59:59# Then
If CurrentProject.AllForms("Form2").IsLoaded Then
If IsDate(Forms!Form2!txtDate) Then
Me.Field1 = DateValue(Forms!Form2!txtDate) + Me.Field1
End If
End If
End If

(The conditions there also elimnate nulls.)
 
Allen,

Thanks for your help. I used the code you suggested, but dealt with null
values at the AfterUpdate event of each field and created an 'AddDate' public
function:

AfterUpdate Code:

If Not IsNull(Field1) Then
Me.Field1 = AddDate(Field1)
End If

AddDate Function:

If Field1 >= #12:00:00 AM# And TimeField <= #11:59:59 PM# Then
If CurrentProject.AllForms("Form").IsLoaded Then
If IsDate(Forms![Form]![txtDate]) Then
AddDate = DateValue(Forms![Form]![txtDate]) + Field1
End If
End If
End If

Thanks again.


Allen Browne said:
Suggestions:

1. Change the name of the text box on the other from to (say) txtDate.

2. If txtDate is unbound (not bound to a date/time field), set the Format
property of the text box to Short Date or similiar, so it won't accept
non-date values.

3. Make sure you don't assign a non-date value to txtDate (e.g.
programmatically, or by its default value, etc.)

4. In your code, make sure the other form is open, and that txtDate contains
a valid date.

5. You may also want to check that Field1 is actually a time value without a
date, and that txtDate is a date without a time component.

This kind of thing:

If Me.Field1 Between #00:00:00# And #23:59:59# Then
If CurrentProject.AllForms("Form2").IsLoaded Then
If IsDate(Forms!Form2!txtDate) Then
Me.Field1 = DateValue(Forms!Form2!txtDate) + Me.Field1
End If
End If
End If

(The conditions there also elimnate nulls.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

DEI said:
I have a form with fields for entry of times in short time format. I would
like the user to enter only the 'short time' only, eg. '13:24', but then I
would like to actually store the data in the underlying table with the
complete date and time, eg. '7/24/2008 1:24 PM'.

I have code in the after update event that adds the date to the time:

Me.field1 = field1 + [Date]

However, the [Date] is actually a date entered in a field on another open
form that is an unbound control, in short date format. I sometimes get a
'type mismatch' error; the date entered in the unbound control is
recognized
as text and not a date.

What is further problematic is that it does not always happen on all
workstations, i.e some users get the error and others do not.

Is there anything I can do to keep this from happenning?

Thanks in advance.

DEI
Click to expand...
Click to expand...
 
Back
Top