Evaluate Date/Time Fields For Existence of Times

[misscrf]

I have some data that I need to run date diffs on. The catch is that for some of this data, there is a date value in one of the fields, with no time. It might look like this:

ROW DT1 DT2
1 1/1/08 8:00 AM 1/1/08 10:00 AM
2 1/2/08 9:00 AM 1/2/2008
3 1/3/08 10:00 AM 1/3/08 12:00 PM
4 1/4/2008 1/4/08 1:00 PM
5 1/5/08 1:00 PM 1/5/08 2:00 PM


So row one can calculate at 2 hours. Row 2, cannot (I have another column to take over when this is the case). Row 3 is good, 4, I need to override. etc.

Can anyone please help me on how I stipulate this condition in an access query?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Can you be more specific regarding your desired SQL, table structures so we know "I have another column to take over when this is the case".

You can test for the presence of the time element with an expression like IIf(DT1 = DateValue(DT1),...,....)

Duane
Hook'D on Access
MS Access MVP

 

[PaulBricker]

Date/Times are stored as Double precision datatypes where the integer value is the date and the decimal value is the time. So if an expression like
MyDateField - Int(MyDateField)
produces a result greater than 0 there is a time value attached to the field. If it's 0 then there is no time attached to the field.
You should be able to test for the value and then do what you need to to make it work for you.

Paul

 

[misscrf]

Thanks, Paul! That should do it.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MichaelRed]

a small caution

While PaulBricker's suggestion works, it must be done pretty strictly as he shows. Newer (younger?) programmers might tend to use the more recent 'enhancements' to the language [e.g. CInt(...)]


Enter the following in the immediate (aka Debug) window
dtStart = Now - 0.2979
dtEnd = Now

this expression returns an error
? CInt(dtEnd) - dtStart

This one follows Paul's dictum - and returns the nonzero result
? Int(dtEnd) - dtStart
-3.50046296807704E-03

'_________________________________________________________

I do not quite follow the difference in terminology between Selections (2) and (4). In either instance, one of the values includes the 'time' portion and the other doesn't. Both need to be handled in an exception routine.

On the other hand, the exception routine must either disallow the calculation or supply an artificial time portion.

While I generally prefer to NOT modify (valid) data entry values, you could (perhaps?) include the time portions in calculated fields (your extra field to "handle" case (2)?) so the situation would seem to only call for addition of the calculated fields along with the slightly complicated query logic to use the appropiate calculated field when the datafield(s) do not include the time.




MichaelRed

 

[SkipVought]

This one follows Paul's dictum - and returns the nonzero result
? Int(dtEnd) - dtStart

Paul'd example was with regard to ONE DATE FIELD, to determine if there is a TIME VALUE.

Your tutorial was with regard to TWO DIFFERENT Date/Time values. I don't get it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TheAceMan1]

Howdy misscrf . . .

[blue]Date/Times are stored as Double precision datatypes where the integer value is the date and the decimal value is the time. So if an expression like
MyDateField - Int(MyDateField)
produces a result greater than 0 there is a time value attached to the field. [purple]If it's 0 then there is no time attached to the field.[/purple][/blue]

[blue]PaulBricker[/blue] has the idea but went astray on his last sentance.

[blue]A time value of zero represents 12:00 AM![/blue]

So yes, there is a representative time for zero! ... 12:00 AM, and herein lies your problem. [blue]DateDiff[/blue] stipulates that Date2 has to be later than Date1 (thats Date + Time), otherwise [blue]a negative value is returned if Date2 is earlier![/blue] ... The value is correct, its just negative!

If your with me so far, the following should be all you need:

[blue]   ABS(DateDiff("h",[Date1],[Date2]))[/blue]

[blue]Your Thoughts! ...[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MichaelRed]

I was PRIMARILY illustrating the difference in results between Int and CInt intrinsic functions.

While the poster was interested in the individual dates, hte process CLEARLY referencces TWO dates. The existance (or lack thereof) of the fractional (time) portions needs to be addressed for either / both fields.



MichaelRed

 

[PHV]

Michael, don't use CInt but CLng with dates !

 

[TheAceMan1]

Sowhat happen to [blue]misscrf?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MichaelRed]

obviously dazed and confused by the performance of the responders? or just satisfied w/ Pauls soloution.



MichaelRed

 

[PaulBricker]

I've been out of town for a few days but it's interesting to see this thread still garnering suggestions. All very good stuff in my opinion.

Paul