Yet another date/ time question 2

[Doc94]

I have taken a few folks advise and created a date field that defaults to todays date and a time field that remains an enterable field on the form. However, I still need to have the date/ time field as I have multiple queries(hundreds) based on it so I am looking to this group for an answer-
On the form or the table, how wouled I combine the dat field and the time field so that it defaults to the date/time field.
I have tried setting the default value for the field in the properties as
=[forms]![fdetailbyfacility]![arrivaldate].[arrivaltime]
Of course this does not work. Any suggestions? The arrival date and arrival time field are entered first, how do I default the date/time field to these two???
Thanks

 

[scriverb]

Could you explain this a little further please. From what you have said you have a date/time field in a table which you will use to query your records. On the form you have two controls: a date control and a time control. You want to combine these two controls into one field and display it as one???

You see this doesn't make any sense the way I am interpreting your posting. You see a date/time field in a table is stored as a decimal number. The whole number portion to the left of the decimal stores you date information. The fractional portion to the right of the decimal stores the time of date starting at 12:00 am as .0000 increasing throughout the day. When you enter data into a control based upon a preset format which includes both the date and the time the information will be stored in this one field for you. You can query against this using both the date and the time portions.

Please post back with your description of what I have missed here. I will try to assist you when I better understand the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BobJacksonNCI]

Doc94,

=Now() will store your PC's current date + time in a control.

If that doesn't specifically answer your question, maybe understanding how Access stores and uses date/time would help. The following contains hotlinks to Microsoft Knowledgebase articles about this.

thread705-650847

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[logicmystic]

If all you want to do is concatentate the fields, for what ever reason, (sometimes you don't want to store the time in the same field as the date for query purposes).

If you have an unbound field on the form named dtm your code would be:
dtm =[forms]![fdetailbyfacility]![arrivaldate] & " " & [forms]![fdetailbyfacility]![arrivaltime]

N.

 

[Doc94]

I'll explain a bit further.
I have a form called fdetailbyfacility. The fields in the form are bound to a table. Currently I have a date/time field called Hospital Arrival Time that , when entered, is in the format **/**/** **:** and the user has to enter the date and the time.
What I had originally wnated was a way, when the field was entered, to have the date portion (**/**/** default to today (=Date()) and leave the time portion so the user enters the time(**:**) because the time portion is not always "now". My previous posts on this suggested to break the date/time field into two fields which I have done- hence ArrivalDate and ArrivalTime.
BUT, I still need the original date time field in the form and hence the table populated with the product of the ArrivalDate and ArrivalTime fields in the form or table as it is used in all of my multiple (100's) of queries.
Hope this explains it a bit better. My data input folks are counting on me to fix this to help them be more efficient. I had originally written the database assuming the data would not be entered "real Time" but now the records are all entered fairly contemporaneously except for the time.
Thanks

 

[BobJacksonNCI]

No solution from me, but I DO want to see how this turns out!

Intuitively, it seems to me there are two choices:

1. Use one field, leave the table default value blank and set the form properties for the field: Default value = now(), Format = mm/dd/yy hh:nn, Input mask blank. When the user wants to enter the time, he/she selects the field, pushes the keyboard "end" key and backspaces over the time to reenter, leaving the date intact. You might be able to write VBA code to put in the Got Focus property which will put the cursor at the end of the field so the user doesn't have to push the "end" key.


2. Continue as you're doing with two fields, but do not bind them to table fields. Then add a button and VBA code to combine them and update one table field. More code this way and I'm not sure how to do it, just confident that it can be done.

Either of these results in date/time in one table field for simpler queries.

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[BobJacksonNCI]

Or, maybe there's another way to consider...

On the form, add a field (control) and plug in how long the patient waited before being entered into the computer. Then, the date/time could be loaded with now() and if you needed to know when the patient arrived, just subtract the number of minutes they waited. Sure, not completely accurate - BUT this has the added benefit of encouraging admittance staff to work quickly.

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[Doc94]

All good ideas.
I tried part of your one suggestion Bob and had the date default to Now. I set an event procedure on the "GotFocus" for the field to use the SendKey{F2}{Right}{Right}{Right}{Right}{Right}{Right}{Right}{Right}{Right}{Right}
Which puts me at the start of the time portion when the field is entered. However, I am unable to enter the date as it brings up the message about not in the correct format for the field. Any ides how to change this?

 

[BobJacksonNCI]

Doc94,

I'm so curious about this I'm even checking the thread from Home! On Saturday!!

Seems to me there would be lots of folks who would like to enter the time and have today's date plugged. Therefore, it stands to reason that some folks have figured it out...
If so, please post and resolve this.

The only two restrictions are that it would get way complicated in a 24 hour environment and that data would go wacko if the PC boot date is wrong.

Hints to problem solvers who have looked at this:
Review Thread705-650847 for how date/time fields are stored.
I created (just now) a table containing three fields;

NAME FORMAT PROPERTY
date/time General Date
date only Short Date
time only Short Time

Then built a form to enter them.
Entered "9/13/03 09:00", "9/13/03", "09:00"

Of course data was displayed as dictated by the Format property.

Then I exported the table to a text file. Results:
9/13/2002 9:00:00 9/13/2002 0:00:00 12/30/1899 9:00:00

There have to be several VBA methods to take the time only and plug today's date...

And if someone doesn't put me out of my misery, I'll have to figure it out on my own!

(Calling Bob Scriver, Calling Bob Scriver...
Are you out there, Bob?)

Waiting with all of the patience of a buzzard circling something that is barely breathing.

 

[danvlas]

Just tried it with a Text0 textbox on a form:
Field format: general date (both date and time)
Default value: Date() (to store just the date, without time)

When textbox gets the focus, checks if the time value is 0 and if yes places the cursor in the Time position.

Private Sub Text0_GotFocus()
If TimeValue(Me.Text0) = 0 Then Me.Text0.SelStart = 11
End Sub

Change 'Text0) to the name of your control.

The only thing is that when the time is exactly minight, it will be interpreted as 0 and it will go there. Workaround: check if it's a new record.


Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Doc94]

I will try this on my form and see if it works.
I have not mentioned that the form is setup such that it has an add records mode in which the records are new records and an edit record mode in which all records are shown.
I will test this.
I have also tried logicmystics solution
dtm =[forms]![fdetailbyfacility]![arrivaldate] & " " & [forms]![fdetailbyfacility]![arrivaltime]

which "works" however it requires the addition of a bunch of new fields.
Please continue the solutions on this topic as I think it will help all.
Thanks
Doc94

 

[Doc94]

danvlas-
I tried your suggestion and get a runtime error with the invalid use of Null???
I will play with it but not sure what is happening with this.

 

[Doc94]

I have played with it and am still getting runtime error 94
Invalid use of null.
This points to the GotFocus of Text0(me!Text0)= 0
Any answers to this?
I have tried changing the format, changing the 0 to #0:00# as wiell as others and nothing seems to work.
Thanks

 

[BobJacksonNCI]

Doc94,

Had to take the wife out - she has her divorce attorney's number on speed dial and says Tek-tips amounts to "alienation of affection". (Grin)

Dan pointed us in the right direction, not surpisingly!
(Dan, remember me? thread700-554700)
I think the null error is because the default value is not loaded until you advance to the next record.

This is probably the best I can do.
Once the data entry field receives focus, the user would have to type a space followed by the time. Note that decimal works as well as colon to separate hours from minutes.

In the table, the field should be defined as Date/Time.
In the form, the Format property is General Date.
The "On Got Focus" event for the control (field) contains:

If IsNull(Me.dtm) Then
Me.dtm = Date()
Me.dtm.SelStart = 11
End If

When the control receives focus, it will show the current date. Today = 9/13/2003. Note the cursor is positioned immediately following 2003. At that point, you can enter a space followed by the time in the form of hh.mm.ss am/pm with only hh.mm required. You can experiment with:
Me.dtm = Now()
but after entering the current time you have to delete the time loaded by Access. I couldn't figure out how to make the time entered overwrite the existing time. It seemed like I should be able to use the Input Mask property, but wasn't able to create anything useful.

I WILL continue to monitor this thread for further developments!

Bob
"It ain't easy, it's Access"

 

[danvlas]

Hmmmm...just tried binding the control to a field and it works, but not as i'd like it...That's because dates are actually numbers and the input mask behaves differently for dates than for text.

You could however use the two fields you mentioned in the first post to enter data and update the Date+Time field you use in the hundreds of queries:

Sub Form_BeforeUpdate()
Me(DateAndTimeField) = Format(Me("DateField&quot,"dd-mmm-yyyy&quot & " " & format(Me("TimeField&quot, "hh:nn&quot
End Sub

Don't worry about the date format ("dd-mmm-yyyy&quot. That's to make sure it's interpreted correctly by VB.

I don't like this setup, as it has a redundant field that has to be recalculated every time you change something, but I think it's your best shot for the moment.
That would ease user's data entry. In time, you should consider changing the 'hundreds' of queries to be based on the concatenated value of the date and time fields:
In those queries, instead of
DateAndTime you should have

[DateField] & " " & [TimeField]

or, to be more catholic than the Pope:

Format(DateField,"dd-mmm-yyyy&quot & " " & Format(TimeField, "hh:nn&quot


When you're done, delete the DateAndTime field from the table and the code that calculates it. Or leave it, but NEVER use it again...painless, but wasting some space.

Bad design always pops up when you expect it least, so you can say you've learnt it the hard way.

P.S.
You could do it directly if you changed the DateAndTime field to be a text, but there is no guarantee that the date will be interpreted correctly in the queries.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Doc94]

Bob-
Still no luck, but with the IsNull at least the date gets put in. Howvere, even using Me.HospitalArrivalTime=Date() gives both the date and time and the cursor does not whow up where it should. Also, my concern with the time portion without formating or validation rules is there will be a variety of information put in. And garbage in= Garbage out!

I will keep working on it later. Not sure how but perhaps in code I need to define the date portion and then define the time portion of the field and if the time portion (?My.Time????) is null then select start=11???
Thanks

If you can't measure it you can't control it.

 

[BobJacksonNCI]

Doc94,

You're seeing something different than I am...

I'm using Access 2000 - your version?

If you'll share your e-mail address, I can send you the test database. 148k unzipped.

BTW, since the field is defined as date/time in the table, invalid times ARE caught here. Not the most user-friendly error message, but easy to figure out what's wrong.

Bob
My e-mail addy is robertj_18840@yahoo.com
Not much space remaining (on my To Do list), so don't send your database.

HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[Doc94]

I think I have the solution but it requires separate fields.
Thanks for all the input.

 

[Doc94]

Here is my final deployed today solution for this topic.
To review, I had wanted a way to default a date to a field but not the time so the user could enter the time. we had a decent discussion on the topic but in the end, after no concrete solution was found, decided on a different route. As you may recall I needed to retain the Date/Time field as I have 100's of queries based on these.
New Fields
Arrival Date (Short Date format, Default=Date())
With the following event procedure:

Private Sub ArrivalDate_AfterUpdate()
[HospitalArrivalTime] = [Forms]![fDetailbyFacility]![ArrivalDate] & " " & [Forms]![fDetailbyFacility]![ArrivalTime]
End Sub

Arrival Time (Short Time format, Inoput mask= 00:00;0;*)
With the following event procedure
Private Sub ArrivalTime_AfterUpdate()
[HospitalArrivalTime] = [Forms]![fDetailbyFacility]![ArrivalDate] & " " & [Forms]![fDetailbyFacility]![ArrivalTime]
End Sub

Both of these fill in the date and time in the HospitalArrivalTime field.

Hospital Arrival Time(Original field retained)
With the following event procedure to check for a valid time:
Private Sub HospitalArrivalTime_Exit(Cancel As Integer)
On Error GoTo Err_HAT_Click

If TimeValue(Forms!fDetailbyFacility.Controls!HospitalArrivalTime) <= Application.Forms!fDetailbyFacility.Controls!RegistrationTime Then
Application.Forms!fDetailbyFacility.Controls!AdmitFrom.SetFocus

Else
If MsgBox(&quot;Hospital Arrival Date/Time is later than Registration Time. Is this OK? &quot;, vbYesNo) = vbNo Then
Application.Forms!fDetailbyFacility.Controls!HospitalArrivalTime.SetFocus
Else
Application.Forms!fDetailbyFacility.Controls!AdmitFrom.SetFocus
End If
End If

Exit_HAT_Click:
Exit Sub

Err_HAT_Click:
MsgBox Err.Description
Resume Exit_HAT_Click
End Sub

Thanks for your input Bob as the db you sent really let me know I was on the right path.
Any ideas on a validation rule so users will enter a time and not leave it blank?? I have tried Not Like Null but it does not work.
Thanks
Doc94

 

[BobJacksonNCI]

Doc94,

Sorry it's taken so long to reply. Got busy yesterday and just now able to get into Tek-tips. Also, I'm doing mid-month financials - I hope the following helps because I may not be able to check back until tonight.

In an Before Update event for the FORM you could put in something like:

If nz([Forms]![fDetailbyFacility]![ArrivalTime],0) = 0 Then
msgbox &quot;You must enter the time&quot;, vbOKonly, &quot;TIME NOT ENTERED&quot;
[Forms]![fDetailbyFacility]![ArrivalTime].setfocus
DoCmd.CancelEvent
End If

I like nz because it lets you check for both null and zero at the same time. Even more complete is len(nz(control)) = 0 as that also checks for zero-length string but I'm uncertain on the syntax.

Good luck, Jon!
Bob