Stripping Minutes From Hour When Only 00., Access 2010

[jamiers]

My supervisor wants me to create the following field. I need to merge fields and format to get it done.

Field 1: Date and Start Hour Training
Field 2: End Hour Training

New merged field...output should be something like this
Sa 9a-4p
M 9:30a-4:15p
Th 10:00a-1p

I have created a lookup for the day -- so I am not concerned about that... Look up Saturday, return Sa... look up Monday, return M.

But... what I can't figure out is how to dump the minutes if ONLY equal to 00 (9:00 should come back 9a, and 4:15 should come back as 4:15p).

This what I have going:
TrainingDayTime: [ShortDay] & " " & [TrainingRange]

Short Day is the lookup for the DayCode (Sa, Su, M, Th)
Training Rage field shows this expression: TrainingRange: Format([Start Date/Time],"h:nna/p") & "-" & Format([End Date/Time],"h:nna/p")

What can I do to those nn's to get them to only show if it is minute 01 to 59 and not 00?

I don't know anything about SQL or how to put it into a query field -- just want to say that FWIW

 

[xenou]

Off the top of my head I think this would be as good as anything:
REPLACE(TrainingRange: Format([Start Date/Time],"h:nna/p") & "-" & Format([End Date/Time],"h:nna/p"),":00","")

 

[Joe4]

Cross-posted here: Stripping Minutes From Hour When Only 00., Access 2010

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.