DateOnly or Short Time formatting

[BobCole]

I have to format a database from a community calendar for a website.

Under "Sdate," the date field, some entries are date-only. Some are date-and-time. By default, the date-only entries display as date-only, which is fine. But the date-and-time entries display with hours, minutes, seconds, and am/pm. I simply want to get rid of the "seconds" entry.

But when I tried using Format([Sdate],"mm/dd/yy"", ""hh:nn am/pm&quot, I got an unwanted result: the date-only entries are now displayed as "12/02/02, 12:00 am." In other words, I get "12:00 am" added to every entry that lacks a specific time.

Is there a way to display the date-only entries as date-only, while shortening the date-and-time entry to ShortDate/MediumTime?

Thanks!

-- Bob

 

[freewilly]

If you don't need the time you can change the field to Short Date in the table design. This will drop the time off every date

 

[BobCole]

Thanks -- I do need to keep the time when it has been included. I found out how to do it though:

SELECT IIf(TimeValue([sdate])>"12:00:00 am",Format([Sdate],"mm/dd hh:nn am/pm&quot,[sdate]) AS DtTm, Events.Description
FROM Events
WHERE (((Events.Description)>"0&quot AND ((Events.Sdate)>Now()-1));

Thanks again for your support.

-- Bob