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.


"mabond" <(E-Mail Removed)> wrote in message
news8D1E037-5B40-4DCC-A3F7-(E-Mail Removed)...
>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

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")
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


"mabond" wrote:

> 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

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

"Ofer" wrote:

> 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")
> --
> The next line is only relevant to Microsoft''s web-based interface users.
> If I answered your question, please mark it as an answer. It''s useful to
> know that my answer was helpful
> HTH, good luck
>
>
> "mabond" wrote:
>
> > 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

If its OK then add to it 1 second

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


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


"mabond" wrote:

> 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
>
> "Ofer" wrote:
>
> > 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")
> > --
> > The next line is only relevant to Microsoft''s web-based interface users.
> > If I answered your question, please mark it as an answer. It''s useful to
> > know that my answer was helpful
> > HTH, good luck
> >
> >
> > "mabond" wrote:
> >
> > > 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

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

"Ofer" wrote:

> If its OK then add to it 1 second
>
> !opact_logindt = format(!opact_logoutdt ,"dd/mm/yyyy 00:00:01")
>
>
> --
> The next line is only relevant to Microsoft''s web-based interface users.
> If I answered your question, please mark it as an answer. It''s useful to
> know that my answer was helpful
> HTH, good luck
>
>
> "mabond" wrote:
>
> > 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
> >
> > "Ofer" wrote:
> >
> > > 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")
> > > --
> > > The next line is only relevant to Microsoft''s web-based interface users.
> > > If I answered your question, please mark it as an answer. It''s useful to
> > > know that my answer was helpful
> > > HTH, good luck
> > >
> > >
> > > "mabond" wrote:
> > >
> > > > 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