Zulu Date / Time Problem

[jamason07]

Here's one for you IFF Then gurus. I have a form that the user fills out which time/date stamps the form using the computers date/time setting. The information needs to be reported in Zulu time. Thus, I have set the [Time] field to calculate with Time()+#05:00:00# to give me Zulu time. However, when the calculation goes past midnight, I need the date to roll to the next day. I've tried this formula in the [Date] field but it doesn't seem to work:

IFF([Time]>#23:59:59#, [Date], [Date]+1)

All this seems to do is change all my dates to the next day, not just the one for the specific record. Suggestions?

Thanks!

Jeffrey

 

[THWatson]

Jeffrey
I am wondering about the possibility of having an additional unbound text box which is calculated from your Date field which the user fills out.

If so you could put some code in the After Update event of your Date field, which would make use of the DateAdd function.

For example this would add one day to the date in the YourDate field.
Me.ZuluDate = DateAdd("d", [YourDate], 1)

What you have to do is work out the logic of reading the time in [YourDate] so if the Time is greater than 2 minute to midnight the [ZuluDate] advances 1 day.

Hope that helps point to something useful.

Tom

 

[evalesthy]

I remember seeing this related thread the other day - see if it helps you any.

thread702-671133

 

[jamason07]

I read the thread. I wish I had had that information when I set up this database. I would have set everything to use seconds from 1/1/70. That would have made life easy. I don't if I can go back from here since I have so much data in the tables now. But that will be helpful for future reference. Thanks!

 

[PaulBricker]

How is your date/time stamp recorded? Is it just the time or is the date included in the same field or are they in separate fields?

Paul

 

[jamason07]

I have them in separate fields.

 

[PaulBricker]

So to put this all in a new field in your query you could use
MyTimeToZuluateAdd("h",5,CDate([DateField] & " " & [TimeField])


That should give you a value you can work with.

Paul