change value of date time field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can't belive i can't find the right way to do this ... think I'm now
blinkered.

I have a table with two date/time fileds representing strat and end times of
an event. Some events span midnight. In that event i want to change the event
starttime to the date of the endtime and midnight. Every thing I have tried
results in only a date value (with long date format) and no time in the field.

With CurrentDb.OpenRecordset("logintimes", dbOpenDynaset)
Do Until .EOF ' To step through all records
.Edit
If DateValue(!opact_logindt) < DateValue(!opact_logoutdt) Then
newdate = DateValue(!opact_logoutdt)
newtime = "00:00:00"
newentry = newdate & " " & newtime
!opact_logindt = newentry
.Update
.MoveNext
end if
Loop

Any advice

Michael Bond
 
The time is there, but since it is exactly midnight you are only being shown
the date. Dates and times are stored as doubles with the integer portion
being the date and the decimal portion being the time. Just like a number
123.000 will only display as 123, a date will only show the time portion (by
default) if the time is something other than 00:00:00.

You can force the time to appear by setting the format to show the time or
the date and the time.
 
Try this

!opact_logindt = format(!opact_logoutdt ,"dd/mm/yyyy 00:00:00")

Or
!opact_logindt = format(!opact_logoutdt ,"mm/dd/yyyy 00:00:00")
 
Hi Ofer

no joy I'm afraid

opact_logindt = 02/11/2005 23:47:16
opact_logoutdt = 03/11/2005 00:01:42

opact_logindt becomes only 03/11/2005 after running the code with your
suggestion of forcing the format to "dd/mm/yyyy 00:00:00"

any other thoughts on what I might be doing wrong

Michael Bond
 
If its OK then add to it 1 second

!opact_logindt = format(!opact_logoutdt ,"dd/mm/yyyy 00:00:01")
 
Ofer

I must be getting better at this. While waiting your reply I followed J
Spencers advice and simply used the value that was showing when I came to my
next calculation. Although the time did not show it did calculate as John
suggested as if it were there. So the events at which ended on day 0 but
started on day -1 were dealt with.

That led me to the events at the end of the day which went in to the next
day (those which started on day 0 and ended on day +1) and there the problem
deepened. Won't go into details, leave it to your imigination (cos my head
hurts thinking about it) but I settled on setting those events as ending at
day 0, 23:59:59 (i.e. - 1 second). For my purposes that was sufficient. So in
a sense both of us thinking along the same lines.

Thanks both for taking the trouble to look at the problem for me. Appreciate
the help. The project is now doing what it needs to do.

Thanks and regards

Michael Bond
 
Back
Top