Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 938
  • Last Modified:

Format Date field in Access

Hello,

I have a field in an Access Form that pulls from a date field in a SQL database.  The format is like this:

2013-12-25

The problem is that I want to format the field in Access to look like this:

12/25/2013

but still be able to edit it with in the Access form if I want.

Thanks,
Joel
0
Genius123
Asked:
Genius123
  • 3
1 Solution
 
YZlatCommented:
you could use Format function

Format (MyDate, "mm/dd/yyyy")
0
 
datAdrenalineCommented:
Using a function to display the value the way you wish will render the value un-editable.  you will want to set the Format property of the TextBox control used to display the Date/Time typed value with this:

mm/dd/yyyy
0
 
datAdrenalineCommented:
If you are looking at the table contents in DataSheet view, then you will have to go into Design Mode of the Table object and change the Format property of the Date/Time field in question.  Do take note, however, that the format of how a Date/Time datatype field (column) is display has no effect on how the value is stored.  Date/Time values are always stored as a serial number that represents the number of days away from a 'base date'.  The base date (a serial number value of 0.0) is 1899-30-12 12:00AM in the scope of VBA and an Access Database Engine file.  In SQL Server, the base date is 1900-01-01 12:00AM, but when you create a Linked Table object in the Access UI environment, the ODBC driver knows how to handle this discrepancy in serial number base dates when viewing/using you data through the Linked Table object.  Also, note that with the serial number, the whole number of the floating point value represents the number of days away from the base date, the fractional part represents the time of day (the portion of the day away from midnight).
0
 
Eric ShermanAccountant/DeveloperCommented:
For the Date Text Box Control on your form, set the following properties:

1.) Input Mask .....  99/99/00;0;_
2.) Format ....  mm/dd/yy


ET
0
 
datAdrenalineCommented:
I personally really really dislike input masks! ... :) ... The Access UI does a pretty good job with out them.  With out an input mask, the user can enter a date in several valid date formats and the Access UI will "know" that it is a date and accept it, and display the value in the format indicated in the Format property (if it is set) or in the default format for that datatype.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now