Display of hour extracted using DatePart in a report

[crownofbees]

Hello,

I'm working on a report based on a crosstab query. The idea is to
display totals across fields by hour, so for the crosstab row heading,
I'm using the expression DatePart("h",[Time]). This is working
perfectly, except for one thing: the DatePart function seems to extract
the hour as an integer, so the hour column in the report is displaying
an integer (e.g. "13") when I would like it to be displaying the hour
in 12-hour AM/PM format (e.g. "1 PM" instead of "13"). Does anyone know
how I can perform this kind of formatting conversion?

Thanks for any assistance,

Erik.

 

[tina]

try

Format([Time],"h AM/PM")

btw, if you really have a field in your table named "Time", recommend you
change it, or at least don't use that name in future tables. "Time" is an
Access reserved word, and as such it shouldn't be used for anything that
*you* name in the database.

hth

 

[crownofbees]

Thanks very much for the suggestion--it worked like a charm. Thanks
also for the tip about avoiding fields called Time--this database is
still in the playing-around stage, so I'll def. change it.

Cheers!

Erik.

 

[tina]

you're welcome, on both counts.
btw, Access has a lot of reserved words, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords
for more info. a tip: to avoid accidentally using a reserved word that you
didn't realize is reserved, add a prefix or suffix to every "name" that you
assign in a database (i usually use 1 to 3 letters, in a prefix).

hth

 

[crownofbees]

This problem has, alas, revealed a new wrinkle. When I was playing with
it over the weekend, I was only dealing with data with hour ranges
between 1PM and 5PM, so everything looked fine in the report. Today,
though, I'm working with data with both AM and PM times, and I'm
finding that the report is sorting based on the first digit of the time
field, like this:
https://netfiles.uiuc.edu/ekraft/www/report_screwy.gif

This is what my query looks like:
https://netfiles.uiuc.edu/ekraft/www/query.gif

I know that what I'm trying to do (get hourly totals across categories,
with the time displayed in the report in 12-hour format and the rows in
ascending hourly order) must be pretty simple, so if anyone would be
kind enough to point out the errors of my ways I'd be grateful.

thanks,

Erik.

 

[crownofbees]

Ah, more googling answered my question. This page suggested the
solution:
http://www.pcreview.co.uk/forums/thread-1182364.php