Format Time field with AM/PM (1 Viewer)

Kayleigh

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
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

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,359
Hi. Data type and Format are two different things. What is the data type of the column in SQL Server?
 

Kayleigh

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
Data type is time (I thought that would be better than datetime which defaults to 1899).
 

Kayleigh

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
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?
 

Attachments

  • Screenshot 2021-12-14 160528.png
    Screenshot 2021-12-14 160528.png
    9.8 KB · Views: 197

bastanu

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
Where are you trying the different formats? You should do it in your form.
 

oleronesoftwares

Passionate Learner
Local time
Today, 08:07
Joined
Sep 22, 2014
Messages
1,159
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

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
Yes it is. See attached
 

Attachments

  • Screenshot 2021-12-14 201631.png
    Screenshot 2021-12-14 201631.png
    45.7 KB · Views: 183

bastanu

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
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

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
Interesting! When opening the Access linked table it shows the data type as short text!
 

bastanu

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
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

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
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.
Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
you can still use it "as-is" (string) and compare to your criteria (as string also).
 

bastanu

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
Sorry Arnel but I don't follow, compare what to which criteria?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
i am not talking to you.
if you want to know what i am talking see the posted screenshot.
 

bastanu

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
No, i am addressing the OP, always.
if i will refer to a third person, i will address his/her name.
 

oleronesoftwares

Passionate Learner
Local time
Today, 08:07
Joined
Sep 22, 2014
Messages
1,159

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

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
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

Member
Local time
Today, 15:07
Joined
Sep 24, 2020
Messages
706
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

AWF VIP
Local time
Today, 08:07
Joined
Apr 13, 2010
Messages
1,401
Have you tried to add an input mask matching your formatting to your controls?

Cheers,
 

Users who are viewing this thread

Top Bottom