Time Difference Calculations

[TimTDP]

This has probably been asked before, so please bear with me!

Dim FirstTimeStamp As Date
Dim SecondTimeStamp As Date
Dim TimeInterval As Integer

FirstTimeStamp =07/06/2010 11:54:33 PM
SecondTimeStamp = 07/07/2010 12:24:33 AM

Both of these are in the format: mm/dd/yyyy hh:nn:ss

TimeInterval = DateDiff("n", FirstTimeStamp, SecondTimeStamp)
When I do the calculation, Access converts the format to dd/mm/yyyy hh:nn:ss (these also happen to be my regional settings) and the code bombs because the answer returned is greater than in integer! The correct answer for TimeInterval is 30

How do I get access to retain the correct format?

attached database.
hit the button on the form and see what happens!

Thanks in advance

 

[boblarson]

Try this:

TimeInterval = CInt(DateDiff("n", FirstTimeStamp, SecondTimeStamp))

 

[boblarson]

Okay, I just figured it out. You can't use INTEGER because of the length of time between the dates in your table. You have one for June and then July and it is more than 32,565 minutes between the two.

So your original code will work if you change it to LONG instead of INTEGER.

 

[Brianwarnock]

Bob his problem is in his Date/Time format his local date is dd/mm/yyyy but the data is mm/dd/yyyy, the solution is to wrap a Format around the Dlookups,
Format(dlookup...),"mm/dd/yyyy")
what is puzzling is that you would expect the vba to be happy with US format, but as I have frequently stated that if you are using field names it copes happily with UK formats, its just another confusion we guys have to contend with.

Brian

 

[boblarson]

I guess it would be helpful if there was a way to make people include at least country on their profile so we have a better idea of where they are from which would help with these problems.

 

[Brianwarnock]

I don't know where he is from but he did mention the formats in his first post.

Brian

 

[TimTDP]

I don't know where he is from but he did mention the formats in his first post.

Brian

I am in South Africa, but I don't understand why this matters!
Are you implying that if I develop a database on a computer using regional settings in the mm/dd/yyyy format it will not work on another computer using regional settings in the dd/mm/yyyy format or yyy/mm/dd format.

If you are, then this is absurd!

 

[Brianwarnock]

I am self taught entirely and may not therefore give the definitive answer.

Date/time is stored as a Dble with the integer representing the relative Day number with 1 being 1/1/1900 , and the decimal portion the time. The Format is used for display and input, thus this is what is puzzling.

FirstTimeStamp =07/06/2010 11:54:33 PM
SecondTimeStamp = 07/07/2010 12:24:33 AM

Both of these are in the format: mm/dd/yyyy hh:nn:ss

Access converts the format to dd/mm/yyyy hh:nn:ss (these also happen to be my regional settings)

Access didn't convert the format but used your local settings, if you are not going to use your local settings then you must tell Access, except that when hardcoding dates in SQL or VBA you must use the US format.

I suppose the question we have to ask is why are those date fields not using your local settings.

Brian

 

[DCrake]

If you are, then this is absurd!

No quite the right quotation

If you are, then this is Access!

 

[DCrake]

Brian

Why are you using US format in the UK?

 

[Brianwarnock]

Brian

Why are you using US format in the UK?

I'm not , why did you think I was?

Brian

 

[DCrake]

Originally Posted by TimTDP
FirstTimeStamp =07/06/2010 11:54:33 PM
SecondTimeStamp = 07/07/2010 12:24:33 AM

Both of these are in the format: mm/dd/yyyy hh:nn:ss

Access converts the format to dd/mm/yyyy hh:nn:ss (these also happen to be my regional settings)

Sorry Brian, I misquoted the above to you.

 

[Brianwarnock]

No problem, my worry when I saw that you had posted was that I had made a fool of myself with my explanation and you were having to put it right.

Brian