Average Time query (simple format)

[MottoK]

Hi,

I am trying to setup a basic aggregate query which will give me the average of all the times in my table.

My first query looks at the base table which has a column which contains dates and times e.g.

07/03/2007 13:50:44

and then formats them to long time format like this: Format([TravelStart],"Long Time") - so it ends up like this:

13:50:44

I then have another query where I bring in this field and then choose "Avg" from the Totals row BUT all I get back is "Data type mismatch in criteria expression".

Anyone know how I can sort this? Very much appreciated.

 

[PaulBricker]

The format function returns a value as a String datatype. Try wrapping your expression in something like CDate


CDate(Format([TravelStart],"Long Time"))


Paul

 

[dhookom]

As Paul noted, you shouldn't format your values prior to your final display in a form or report (or maybe your final query). If you want to remove the date portion of a Date/Time value, use the TimeValue() function:
TimeValue([TravelStart])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]