Format Time field with AM/PM

[Kayleigh]

Hi,
Since I've converted to SQL backend I've not been able to display the AM/PM in the fields with times although they are formatted as time data type. I tried the Medium date format as well hh:nn AM/PM but no success.
Its probably an easy one but can anyone suggest how to resolve please?

 

[theDBguy]

Hi. Data type and Format are two different things. What is the data type of the column in SQL Server?

 

[Kayleigh]

Data type is time (I thought that would be better than datetime which defaults to 1899).

 

[Kayleigh]

Please see this screenshot. I've tried every relevant data type but cannot seem to have it format to the specified type. Survey date should be short date; Between/and should be medium time.
How can I go about this?

 

[bastanu]

Where are you trying the different formats? You should do it in your form.

 

[oleronesoftwares]

The formatting should be done either at form level or VBA(after update event of the control on the form).

Since the database is in Ms SQL

 

[Kayleigh]

Yes it is. See attached

 

[bastanu]

What happens if you try to select Medium Date from the drop-down of the Format property? What is the data type for the field in the Access linked table (as mapped by the ODBC driver)?

 

[Kayleigh]

Interesting! When opening the Access linked table it shows the data type as short text!

 

[bastanu]

That's what I thought, that is the reason why the formatting doesn't work. Try changing the SQL data type to datetime instead, I think it should work better. Or if you can wrap it in a CDate() in the controlsource but then you won't be able to easily edit it.

What ODBC driver are you using?

 

[bastanu]

Here is the MS link on SQL server Time data type, mid page it shows that the ODBC data type for down-level clients is SQL_WVARCHAR or SQL_VARCHAR which would be mapped as Short Text by earlier ODBC drivers.

time (Transact-SQL) - SQL Server

time (Transact-SQL)

docs.microsoft.com

Cheers,

 

[arnelgp]

you can still use it "as-is" (string) and compare to your criteria (as string also).

 

[bastanu]

Sorry Arnel but I don't follow, compare what to which criteria?

 

[arnelgp]

i am not talking to you.
if you want to know what i am talking see the posted screenshot.

 

[bastanu]

In my humble opinion when you post something in a public forum you are at least addressing all the previous participants of that thread. I did look at the attachment and all the other posts and did not see any mention of comparing the date with any criteria, it was just about formatting the SQL date as the previous Access one. But I might be wrong....

 

[arnelgp]

No, i am addressing the OP, always.
if i will refer to a third person, i will address his/her name.

 

[oleronesoftwares]

Using the Date/Time Extended data type - Microsoft Support

Date/Time Extended maps to SQL Server datetime 2 data type, has larger precision and date range.

support.microsoft.com

According to microsoft documentation, they have a datetime extended data type to be used when using ms access with ms sql, but this feature is available in ms access 2021 and office 365 only and samples exist to retrieve part of the date.

so @Krayna what version of office are you using?

 

[Kayleigh]

You have hit the nail on the head @oleronesoftwares - I was just about to comment that my FE is running on MS Access 2016. (When I was testing I was using Office 365 so the issue wasn't occurring!)
So as @bastanu advised, I have looked at the linked table in design view. The ONLY SQL data type which would display as date/time in MS Access was the simple datetime. The others I tried would show as short text.

If I would use the other date formats. Would I need to convert to date whenever I require formatting?

 

[Kayleigh]

One more question - the format displays beautifully in the form but when I click into the field it will display the full date (I only wish to see the time). Is there any way around this?

 

[bastanu]

Have you tried to add an input mask matching your formatting to your controls?

Cheers,