Format Fields within Queries
How to Format Fields within Queries in Access
You can format the properties for your query’s fields in Design View so that their data appears in the format you specify. For example, you can specify where Access display the thousands operator in the Number field, format date fields to display different date formats, or format a text field to only display upper case letters.
- In Design View, select the field that you want to format.
- Click the Property Sheet button on the Design tab.
The Property Sheet appears. Here, you can adjust how your fields are formatted.
- Click the Format box in the Property Sheet.
- Type how you want the field to be formatted.
You can also select a format by clicking the arrow in the Format box and selecting a format from the list.
Refer to the tables below to see how you can format fields differently in Access.
| Available Number and Currency Formats | ||
|---|---|---|
| Character | Description | Example |
| # | Displays a digit. Each instance of # represents the position of a number. If no value exists in the position, Access creates a blank space | #### shows the value 35 as 35 #### shows the value 55436 as 55436 |
| 0 | Displays a digit. Each instance of # represents the position of a number. If no value exists in the position, Access displays a 0 in its place. |
0000 shows the value 35 as 0035 0000 shows the value 55436 as 55436. |
| Thousands and Decimals separators (, and .) | Displays a thousands separator (,) or decimal separator (.) in the position you specify. These settings are defined in Windows regional settings. |
##,###.00 show the value 35 as 35.00 ##,###.00 show the value 55436 as 55,436.00 |
| Currency Symbols (¥ £ $) | Displays currency symbols as needed. |
$##,###.00 show the value 35 as $35.00 $##,###.00 show the value 55436 as $55,436.00 |
| Date separator (- or /) | Controls where Access places the separator for days, months, and years. These settings are defined in Windows regional settings. |
m-dd-yy shows the date February 12, 2017 as 2-12-17 m/dd/yy shows the date February 12, 2017 as 2/12/17 |
| d or dd | Displays the day as one or two digits. |
m-d-yy shows the date February 2, 2017 as 2-2-17 m-dd-yy shows the date February 2, 2017 as 2-02-17 |
| m or mm | Displays the month as one or two digits. |
m-dd-yy shows the date February 12, 2017 as 2-12-17 mm-dd-yy shows the date February 2, 2017 as 02-12-17 |
| yy or yyyy | Displays the year as two or four digits. |
m-dd-yy shows the date February 12, 2017 as 2-12-17 m-dd-yyyy shows the date February 12, 2017 as 2-12-2017 |
| ddddd | Displays the date in Short Date Format. | ddddd shows the date February 12, 2017 as 2-12-17 |
| dddddd | Displays the date in Long Date Format. | dddddd shows the date February 12, 2017 as February 12, 2017 |
| q | Displays the current calendar quarter. | q shows the date February 12, 2017 as 2 q shows the date October 22, 2017 as 4 |
| Time separator (: or 1) | Controls where Access places the separator for hours, minutes, and seconds. These settings are defined in Windows regional settings. |
hh:nn:ss shows the time 5:51:00 AM as 5:51:00 hh-nn-ss shows the time 8:31:41 PM as 20-31-41 |
| h or hh | Displays the hour as one or two digits. |
h:nn displays the time 7:30 AM as 7:30 hh:nn displays the time 7:30 AM as 07:30 |
| n or nn | Displays the minute as one or two digits. |
h:n displays the time 7:04 AM as 7:04 h:nn displays the time 7:04 AM as 7:4 |
| s or ss | Displays the second as one or two digits. |
h:nn:s displays the time 7:30:04 AM as 7:30:4 h:nn:ss displays the time 7:30:04 AM as 7:30:04 |
| @ | Displays any available character for its position in the format string. All remaining placeholders will appear as blank text. |
@@@@@ shows the text Jon as Jon with two space in front @@@@@ shows the text Johnson as Johnson |
| & | Displays any available character for its position in the string of text. All remaining placeholders will display nothing. |
&&&&& shows the text Jon as Jon &&&&& shows the text Johnson as Johnson |
| ! | Forces placeholder characters to be filled from left to right instead of right to left. |
! shows the text Jon as Jon with left alignment ! shows the text Johnson as Johnson with left alignment |
| < | Forces all text to lowercase. This must be placed at the beginning of a format string. |
< shows the text Jon as jon < shows the text Johnson as johnson |
| > | Forces all text to uppercase. This must be placed at the beginning of a format string. |
> shows the text Jon as JON > shows the text Johnson as JOHNSON |
| * | Forces the character after the * to become a fill character and fill in blank spaces. |
* shows the text Jon as Jon with right alignment * shows the text Johnson as Johnson with right alignment |
| “Literal Text” | Displays specific text that is in double quotation marks. |
“Jon” shows the text Jon as Jon “Johnson” shows the text Johnson as Johnson |
FREE Quick Reference
Click to DownloadFree to distribute with our compliments; we hope you will consider our paid training.
You Get:
- LinkedIn-ready certificate
- Skill assessments
- Bite-sized, interactive lessons
- Saved progress