Display time in a calculated field

[charlesstricklin]

I'm developing a database to log all support calls that come into my desk, and I store the date and the time in separate fields when the call comes in and a new record is created using the =Now() function. I'm using a calculated field to form what will be the standardized format disposition that I will copy and paste into the billing system that we use.

I'd like the calculated field to display something like 'RC3 11:45 AM CST JOHN CI NO DIAL TONE..." where RC3 is my department, JOHN is obviously the caller's name, etc.

My problem is that when I set the expression as [callTime] & " CDT " it displays in the format 8/25/2016 10:03:49 AM CDT when I do not want to include either the date or the seconds.

I've fiddled with several formatting functions, but nothing works. Can someone explain what I'm doing wrong or what I need to change, please?

 

[Joe4]

To get just the hours, minutes, and AM/PM from your CallTime field, try applying the FORMAT function to it, i.e.

Format([CallTime],"hh:nn AM/PM")

then you can concatenate your other fields/text values.

 

[charlesstricklin]

Thanks, but I get the following error when I put that in the calculated field:

The expression Format([callTime],"hh:nn AM/PM") cannot be used in a calculated column.

 

[Joe4]

The expression Format([callTime],"hh:nn AM/PM") cannot be used in a calculated column.

Hmm... Works for me.

Are you trying to do this in a calculated column in a Query or a Table?
I did it in a Query (I would suggest not using the new calculated field functionality Access offers for Tables).

 

[charlesstricklin]

I was doing it in the table. It'll eventually be in a form, so I guess I'll do it in a query.

 

[Joe4]

I would strongly caution against using the calculated field functionality in a Table, for reasons such as:
- there are limitations on what fields it can access and what functions you can use
- no other database program allows for this, so if you used this and had to migrate your data to a database program like SQL, MySQL, Oracle, etc., someday, it wouldn't work "as-is"

Personally, I find no real use for it at all. Since you can use a Query for pretty much anything you can use Tables for (Forms, Reports, exports, etc), there really isn't any reason to not do it in a Query.
So, I don't really see any benefit to the new feature that allows you to do calculated fields in a Table (just drawbacks and "gotchas" to watch out for).