By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

How to Edit a formatted Date/Time field

P: 30
I have two Date/Time fields[ClockIN] and [ClockOut] in a table called tblTIMES that each store a date and time formatted as (mm/dd/yy hh:nn:ss AMPM).

I use "Format("ClockIn","mm/dd/yyyy")" and "Format("ClockIn","hh:nn:ss AMPM")" to separate the respective Date or Time where I need to do so.

One of those places is a text box on a form that I want to use to edit the Date and/or time of a specific record.

This does not seem to work.

I can successfully display the formatted date or time, but I cannot edit it. Any suggestions?
Nov 19 '11 #1

✓ answered by NeoPa

NeoPa:
In a situation such as this I would recommend a slightly more complex setup :
For each date have three TextBox controls - A bound but invisible control for the whole value plus two unbound ones for showing the Date and Time values separately. Use DateValue() and TimeValue() to set the values in these controls in the Form_Current() event procedure. After either is updated (with valid date or time values respectively) update the value of the bound control to reflect the change.
In accordance with the quoted section above I've written some code that illustrates the type of thing I'm talking about. I use completely different control names from your attached example, but it should get you up and started at least. I'll start with a list of the relevant objects :

Controls :
[txtDateIn]; [txtDIDate]; [txtDITime]
[txtDateOut]; [txtDODate]; [txtDOTime]

You should ensure that only valid entries (which presumably include Nulls) are allowed to be entered into the controls. Entering a Null into a control (or clearing the control) should be treated as a wish to clear the whole date/time value.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     With Me
  6.         If IsDate(.txtDateIn) Then
  7.             .txtDIDate = DateValue(.txtDateIn)
  8.             .txtDITime = TimeValue(.txtDateIn)
  9.         Else
  10.             .txtDIDate = Null
  11.             .txtDITime = Null
  12.         End If
  13.         If IsDate(.txtDateOut) Then
  14.             .txtDODate = DateValue(.txtDateOut)
  15.             .txtDOTime = TimeValue(.txtDateOut)
  16.         Else
  17.             .txtDODate = Null
  18.             .txtDOTime = Null
  19.         End If
  20.     End With
  21. End Sub
  22.  
  23. Private Sub txtDIDate_AfterUpdate()
  24.     With Me
  25.         If IsNull(.txtDIDate) Then
  26.             .txtDITime = Null
  27.             .txtDateIn = Null
  28.         Else
  29.             .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
  30.         End If
  31.     End With
  32. End Sub
  33.  
  34. Private Sub txtDITime_AfterUpdate()
  35.     With Me
  36.         If IsNull(.txtDITime) Then
  37.             .txtDIDate = Null
  38.             .txtDateIn = Null
  39.         Else
  40.             .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
  41.         End If
  42.     End With
  43. End Sub
  44.  
  45. Private Sub txtDODate_AfterUpdate()
  46.     With Me
  47.         If IsNull(.txtDODate) Then
  48.             .txtDOTime = Null
  49.             .txtDateOut = Null
  50.         Else
  51.             .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
  52.         End If
  53.     End With
  54. End Sub
  55.  
  56. Private Sub txtDOTime_AfterUpdate()
  57.     With Me
  58.         If IsNull(.txtDOTime) Then
  59.             .txtDODate = Null
  60.             .txtDateOut = Null
  61.         Else
  62.             .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
  63.         End If
  64.     End With
  65. End Sub
PS.
Seth:
All I did was Save As Access 2003. I didn't even look at the database.
That was still helpful, definitely, but the main plaudits then to Wesley for a sensible example database.

Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,709
Wesley:
This does not seem to work.
It wouldn't, as the control is not bound to your field if you do it that way. It has a value set which you cannot change (as that's exactly what you've told it to do by setting the ControlSource property to an expression rather than a field from the RecordSource of the form).

Suggestion:
Don't separate the date and the time. Use the Format property of the TextBox to lay the data out as you would like and bind the TextBox to the field itself (directly). That way everything just works naturally ;-)
Nov 19 '11 #2

P: 30
I understand, and that totally makes sense! I wasn't thinking that the TextBox has the ability to Format data built in. Thanks!!
Nov 22 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
It's a pleasure Wesley. That's what sharing experience is all about :-)
Nov 22 '11 #4

P: 30
I just realized, This does display the data correctly and allow me to change accordingly. But one small glitch is that when you click inside the formatted TextBox, the entire field is then displayed. Not so user friendly. Is there a way to keep the original formatting from showing up when you click in the TextBox?
Nov 22 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
Can you give examples of what you mean. I'm not following your reference terms.
Nov 23 '11 #6

P: 30
Sure. I have attached a sample database to show what I mean. On the form "Format Dates" I have four date/time Text Boxes. They are each formatted to show either Time OR Date but just try clicking inside one of the boxes to see what happens. In my original database I need to be able to edit the records by changing the text in the Text Boxes. You will see how this could become a nuisance.
Attached Files
File Type: zip DateTimeFields.zip (21.5 KB, 102 views)
Nov 28 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
Sorry Wesley, I wasn't really asking for a databsase to look through, just a few examples to illustrate what it is you're saying.

If however, you feel the best/only way for me to see this is within the context of a database then I'm afraid you'll need to follow the instructions found in Attach Database (or other work). I still use 2003 so I cannot open your ACCDB file.
Nov 28 '11 #8

Seth Schrock
Expert 2.5K+
P: 2,951
Here is the 2003 version.
Attached Files
File Type: zip DateTimeFields.zip (19.2 KB, 77 views)
Nov 29 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
Thanks Seth. I don't know if Wesley's original was pared down the way this is but somebody's done a fantastic job of posting just what was required. I guess it was probably Wesley showing great intelligence, but if it was you Seth, then it was a very clever move. Whoever it was did an excellent job is what I'm trying to say.

Now I understand your point Wesley, and having seen it, I feel I was probably a bit dumb not to get it from your earlier comments (which explained the situation well enough in hindsight).

In a situation such as this I would recommend a slightly more complex setup :
For each date have three TextBox controls - A bound but invisible control for the whole value plus two unbound ones for showing the Date and Time values separately. Use DateValue() and TimeValue() to set the values in these controls in the Form_Current() event procedure. After either is updated (with valid date or time values respectively) update the value of the bound control to reflect the change.

Does that make sense?
Nov 29 '11 #10

Seth Schrock
Expert 2.5K+
P: 2,951
All I did was Save As Access 2003. I didn't even look at the database.
Nov 29 '11 #11

NeoPa
Expert Mod 15k+
P: 31,709
NeoPa:
In a situation such as this I would recommend a slightly more complex setup :
For each date have three TextBox controls - A bound but invisible control for the whole value plus two unbound ones for showing the Date and Time values separately. Use DateValue() and TimeValue() to set the values in these controls in the Form_Current() event procedure. After either is updated (with valid date or time values respectively) update the value of the bound control to reflect the change.
In accordance with the quoted section above I've written some code that illustrates the type of thing I'm talking about. I use completely different control names from your attached example, but it should get you up and started at least. I'll start with a list of the relevant objects :

Controls :
[txtDateIn]; [txtDIDate]; [txtDITime]
[txtDateOut]; [txtDODate]; [txtDOTime]

You should ensure that only valid entries (which presumably include Nulls) are allowed to be entered into the controls. Entering a Null into a control (or clearing the control) should be treated as a wish to clear the whole date/time value.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     With Me
  6.         If IsDate(.txtDateIn) Then
  7.             .txtDIDate = DateValue(.txtDateIn)
  8.             .txtDITime = TimeValue(.txtDateIn)
  9.         Else
  10.             .txtDIDate = Null
  11.             .txtDITime = Null
  12.         End If
  13.         If IsDate(.txtDateOut) Then
  14.             .txtDODate = DateValue(.txtDateOut)
  15.             .txtDOTime = TimeValue(.txtDateOut)
  16.         Else
  17.             .txtDODate = Null
  18.             .txtDOTime = Null
  19.         End If
  20.     End With
  21. End Sub
  22.  
  23. Private Sub txtDIDate_AfterUpdate()
  24.     With Me
  25.         If IsNull(.txtDIDate) Then
  26.             .txtDITime = Null
  27.             .txtDateIn = Null
  28.         Else
  29.             .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
  30.         End If
  31.     End With
  32. End Sub
  33.  
  34. Private Sub txtDITime_AfterUpdate()
  35.     With Me
  36.         If IsNull(.txtDITime) Then
  37.             .txtDIDate = Null
  38.             .txtDateIn = Null
  39.         Else
  40.             .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
  41.         End If
  42.     End With
  43. End Sub
  44.  
  45. Private Sub txtDODate_AfterUpdate()
  46.     With Me
  47.         If IsNull(.txtDODate) Then
  48.             .txtDOTime = Null
  49.             .txtDateOut = Null
  50.         Else
  51.             .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
  52.         End If
  53.     End With
  54. End Sub
  55.  
  56. Private Sub txtDOTime_AfterUpdate()
  57.     With Me
  58.         If IsNull(.txtDOTime) Then
  59.             .txtDODate = Null
  60.             .txtDateOut = Null
  61.         Else
  62.             .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
  63.         End If
  64.     End With
  65. End Sub
PS.
Seth:
All I did was Save As Access 2003. I didn't even look at the database.
That was still helpful, definitely, but the main plaudits then to Wesley for a sensible example database.
Nov 29 '11 #12

P: 30
Once again, this works perfect! Thank you! Next time I will read the Attach Database Instructions.
Nov 29 '11 #13

NeoPa
Expert Mod 15k+
P: 31,709
Not a problem Wesley - particularly considering I only posted the link after you posted the database ;-)

PS. I would suggest that you reset the Best Answer post in this thread and set it to post #12 instead. Now the actual question is clearer I believe that one more directly pertains to, and answers, it.
Nov 29 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.