Graph Time Frequency Plot...

[AJ]

With MS Access 2003, I am trying to create a graph of some events in
our database.

When an event occurs, they are time/date stamped. I can run a graph of
those, but:

How do I make the X-axis on a graph be straight time, so the dots on
the graph appear at varying intervals (reflecting how often the events
occurred).

This is a touch easier in Excel... Do I need to export this to Excel
all the time? or is there a way to do this in Access?

 

[Rob Parker]

Not sure if this is exactly what you're asking ...

To graph only the time portion of your data (rather than date/time), use a
query as the recordsource for your graph. Use a calculated field to show
only the time portion of each date/time field, thus:
MyTime: [MyDateTime] - Int([MyDateTime])

You could also use TimeValue([MyDateTime]); however, this will give #Error
if the datetime field is null. The expression above has no such problems;
it simply strips the date portion (the integer portion of the number
representing the number of days (date) since 30 Dec 1899) from the datetime
value, leaving only the time (decimal portion of 24 hours).

HTH,

Rob

 

[James A. Fortune]

Not sure if this is exactly what you're asking ...

To graph only the time portion of your data (rather than date/time), use a
query as the recordsource for your graph. Use a calculated field to show
only the time portion of each date/time field, thus:
MyTime: [MyDateTime] - Int([MyDateTime])

You could also use TimeValue([MyDateTime]); however, this will give #Error
if the datetime field is null. The expression above has no such problems;
it simply strips the date portion (the integer portion of the number
representing the number of days (date) since 30 Dec 1899) from the datetime
value, leaving only the time (decimal portion of 24 hours).

HTH,

Rob

Rob,

Try:

IIf([MyDateTime] IS NULL, NULL, TimeValue([MyDateTime])) AS MyTime

or

'Module Code
Public Function MyTimeValue(varIn As Variant) As Variant
MyTimeValue = varIn
If IsNull(varIn) Then Exit Function
MyTimeValue = TimeValue(varIn)
End Function

That way you're not dependent on how Date/Time fields are stored internally.

James A. Fortune
(e-mail address removed)

 

[Rob Parker]

Hi James,

Thanks for that.

I know it can be done this way, but in this case the expression I posted is
simpler (and probably faster, since there's no iif() to evaluate). The OP
stated that he had datetime data from event/error logging, so I presume that
the values are entered using a Now() function in code somewhere; if that's
the case, my expression is fine.

The OP might find the alternative approach useful, if his data isn't as I
presume.

Rob

Not sure if this is exactly what you're asking ...

To graph only the time portion of your data (rather than date/time), use
a query as the recordsource for your graph. Use a calculated field to
show only the time portion of each date/time field, thus:
MyTime: [MyDateTime] - Int([MyDateTime])

You could also use TimeValue([MyDateTime]); however, this will give
#Error if the datetime field is null. The expression above has no such
problems; it simply strips the date portion (the integer portion of the
number representing the number of days (date) since 30 Dec 1899) from the
datetime value, leaving only the time (decimal portion of 24 hours).

HTH,

Rob

Rob,

Try:

IIf([MyDateTime] IS NULL, NULL, TimeValue([MyDateTime])) AS MyTime

or

'Module Code
Public Function MyTimeValue(varIn As Variant) As Variant
MyTimeValue = varIn
If IsNull(varIn) Then Exit Function
MyTimeValue = TimeValue(varIn)
End Function

That way you're not dependent on how Date/Time fields are stored
internally.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Hi James,

Thanks for that.

I know it can be done this way, but in this case the expression I posted is
simpler (and probably faster, since there's no iif() to evaluate). The OP
stated that he had datetime data from event/error logging, so I presume that
the values are entered using a Now() function in code somewhere; if that's
the case, my expression is fine.

The OP might find the alternative approach useful, if his data isn't as I
presume.

Rob

Fair enough. I only ask that people have reasons for doing something in
a way that I don't find the best.

James A. Fortune
(e-mail address removed)