Format
By Richard Rost
4 years ago
Change How Data is Displayed with Format Property
In this Microsoft Access tutorial, I'm going to show you how to change the way data is displayed with the Format property and function in Microsoft Access
Links
Suggested Courses
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, microsoft access format, Format Function, Format a text field, foramt a number or currency field, format a date field, format a date time, custom date time formats, format a yes/no field, Microsoft Access Format Function Examples, Format Fields within Queries, Formatting Values
Subscribe to Format
Get notifications when this page is updated
Intro
In this video, we'll talk about the Format Property and Format Function in Microsoft Access. You'll see how to use the Format feature in tables, queries, forms, and reports to control how data like dates, numbers, text, and currency are displayed without changing the underlying data. We'll discuss built-in formats, creating custom formats, differences between using the property or the function, and some common scenarios where formatting is important, including examples for calculated fields and VBA.
Transcript
Welcome to another FastTips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, we are going to talk about the Format Property and the Format Function.
The Format Property and Function allow you to change the way that data is displayed in your database and your tables, queries, forms, and reports. For example, here you can see the customer since field at the date, time, value. Here, I have set the short date format, which looks like that. That is on my system. I have got the ISO Date Standard, which is here, Month Day. Access actually gets that from your Windows Regional Settings. That is set in your Windows Control Panel.
Over here, you can see I have changed it to Long Date, and that is what a long date looks like. So it is just multiple different ways for displaying data. It does not change the actual data stored in the table. It is still a date field.
You can set the Format Property in either a table, a query, a form, or a report. In a table, the properties are below the fields in Design View. As you click on each field up here, you will see the different properties for that field appear on the bottom.
In a query, right click on a field and select Properties to find the Property Sheet. Right click on one of the columns down here, the query fields, go to Properties. On the right side over here, you will see the Property Sheet appear, and there is the Format.
You can do the same thing to find the Property Sheet in Design View for a form or report. Just right click on the field that you want, select Properties, and the Property Sheet appears over here. You get a Format tab with the Format Property.
If you change the Format for a field at the table level, it will carry through to any new forms or reports that you make from that time on, but it does not change existing ones. If you have already got a customer table with no Format and a customer form with no Format, if you change the table, it is not going to update that form. You will have to go into the form and make the same change there.
Personally, I do not usually put a lot of formatting in at the table level. I like to leave that data pretty much raw. But I do put the formatting in my queries, forms, and reports. You can change the format based on your needs.
For example, I might have an order date in my order table that uses a default value of now, equals now for the default value. That is going to put the date and time that the order was placed into the table. On the invoices that I generate, it is only going to show a short date because I do not want to put the time on the invoices. But I can generate a query or another report for management, for example, that does have the times on it formatted this way.
That way you can do a report and see what time of the day most of your orders come in. That is not important for the customer, so you do not need the format there. But for management, you might want that. So you can have different formats for the same field in different places.
When it comes to formats, you can select from a list of predefined formats in Access or you can create your own custom formats. For example, with dates, you can see general date, long date, medium date, short date, and lots more, or you can type in your own using a combination of characters.
The format property uses a different set of characters for each of the different data types. For example, text usually uses an at sign to display a character. For example, let us say I have got the data stored in my customer table for the phone number field just as the digits, which is how I like to do it. I can then set a format that looks like this. The at sign says put one digit there, and then the backslash dash means to put a literal character there – the dash – and then three more, and then a dash, and then four more, and then it ends up looking like that.
There are a lot of different characters that you can use for different things. You can force fields to be capitalized, lowercase, and so on. I cover this in much more detail in my full classes.
Dates will generally use this set of characters, and again, there are more, but these are the basic ones. You have got year, four y's, month, day, hour. That is n because m is already taken so they use n for minute and then seconds.
Here is a custom date format. That is the year, month, day with no separating symbols, and then it comes out looking like that. Again, I spend a lot of time in my full classes on custom date fields.
Numbers and currency values have their own set of formats. A general number just displays like that with no commas and whatever number of decimal places you have got. Currency will look like that – generally it has the thousand separator and two digits after the decimal point. Again, these settings are determined in the Windows regional settings in your Control Panel.
You have got euro, you have got fixed, which allows you to specify a fixed number of digits. You have got standard, you have got percent, which basically multiplies the number in the field by 100 and displays it with a percent sign. And then of course, scientific notation. Yes, there are some custom number and currency formats as well. I do not use those quite as often as I use custom dates though.
Yes/no values can be displayed as either yes/no, on/off, true/false. You can change it depending on the situation.
Sometimes in a query, especially when you set the format using the property in the property sheet, it does not always work. You can do a calculated value and set the property to currency and it still does not show as currency; that happens especially with calculated fields. So you can use the format function if the property does not work.
For example, I have created a calculated field called extended price and it is basically quantity times unit price, but I put that inside the format function. I am going to send it the value right here and then inside quotes, the word currency, or a custom format if you are doing a date for example; you can put mmddyyyy in there.
That format function will take priority and it will usually work if the format property does not. I have seen it happen a lot, especially if you have got multiple queries deep or you have got a query based on a query based on a query, you have aggregates, you have all kinds of other complex stuff going on in there, and for some reason, the property does not work. In that case, use the format function.
You can also use the format function in your VBA, if you do a little VBA programming. For example, I have got a field named phone and I send it to the format property to apply that format, and I set that whole thing into a field called phone number.
Finally, you can actually change the format of a field using the format property of the field in VB for your VB programmers. For example, let us say hypothetically I do business in both France and the United States, and I want it so that if the customer is from France, it formats their credit limit with the euro. Otherwise, for any other country, it formats it with the currency value.
You can see here Will Riker has got a dollar sign, but Jean-Luc Picard has a euro symbol. That is how you would format that in the form current event, which runs when you move from record to record, and it sets the format dynamically based on the data. I cover a lot more of this type of stuff in my developer lessons.
If you want to learn more about the format property, I cover it in depth in my Access Beginner Level 3 class. Expert Level 3 covers custom date formats in more detail. Expert Level 8 goes into currency formats, putting formats in query columns. Expert 10, 11, and 12 each cover different aspects of formatting text, more with custom dates, the format function, and of course lots more. My developer courses go into a lot more detail with changing formats in VBA.
So there is your quick basic rundown of the format property and function. If you have any questions, feel free to post them in the comments section below. We will see you next time. Hope you learned something.
Quiz
Q1. What is the main purpose of the Format Property and the Format Function in Microsoft Access?
A. To change the database structure
B. To alter the way data is displayed without changing the stored data
C. To encrypt data in tables
D. To back up records
Q2. Where does Access get the regional settings for date formats by default?
A. Microsoft Access Settings
B. Internet Options
C. Windows Control Panel Regional Settings
D. The Access database itself
Q3. If you change the format of a field at the table level, which of the following is true?
A. It updates all existing forms and reports automatically
B. It only affects new forms or reports created afterward
C. It deletes the related fields in all tables
D. It does not affect any forms or reports
Q4. Why might someone prefer to apply formatting in queries, forms, or reports rather than in a table?
A. It allows for displaying "raw" data in the table
B. It changes the underlying data in the table
C. Formatting cannot be set in queries or reports
D. Tables should always display formatted data
Q5. Which of the following is a correct predefined date format in Access?
A. Very Short Date
B. Simple Date
C. Long Date
D. Compact Date
Q6. What is the function of the at sign (@) in custom text formats?
A. Inserts the current date
B. Displays a single character or space
C. Adds a percent sign
D. Forces all text to uppercase
Q7. Which character is used to represent "minute" in custom date formats in Access?
A. M
B. H
C. N
D. S
Q8. What effect does applying the "Percent" format to a field have?
A. Divides the value by 100 and shows a percent sign
B. Multiplies the value by 100 and shows a percent sign
C. Removes all decimal places
D. Displays the literal word "Percent"
Q9. When might you need to use the Format Function instead of relying on the Format Property?
A. When only numbers are used
B. When formatting does not apply to labels
C. When calculated fields are not displaying the desired format
D. When your database is password protected
Q10. How can you display different currency symbols for customers from different countries using formatting in forms?
A. Manually edit each record
B. Use the Format property dynamically in VBA code in a form event
C. Set the format in the table and force all forms to update
D. Create a separate database for each currency
Q11. Which of the following can the Format Property and Format Function NOT directly change?
A. The appearance of data on a report
B. The physical value of data stored in a table
C. The way a field is displayed in a form
D. The format used in a query
Q12. What happens if you set a custom format to a phone number field containing just digits?
A. The data is overwritten with the format characters
B. The format is applied for display but the data remains just digits
C. The phone numbers are deleted
D. All leading zeros are removed
Answers: 1-B; 2-C; 3-B; 4-A; 5-C; 6-B; 7-C; 8-B; 9-C; 10-B; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone covers the Format Property and the Format Function in Microsoft Access. These tools allow you to control exactly how your data is displayed in tables, queries, forms, and reports. For example, if you have a "Customer Since" date field, you can display it in different formats such as a short date or a long date. The way data appears depends on the format you select and the regional settings configured in your Windows Control Panel. Regardless of how information is displayed, keep in mind that the original data stored in the table does not change. It is only the presentation that updates.
You can set the Format Property across tables, queries, forms, and reports. In table design view, select a field and you'll see its properties, including format, in the bottom section of the screen. When working in a query, you can find the Property Sheet by right-clicking a field and selecting Properties, usually found on the right side of the screen. The same process applies in forms and reports design view. If you set a format at the table level, any new forms or reports you create afterward will inherit that setting. However, existing forms or reports will not update automatically, so those will require you to apply the same format change directly within them.
Personally, I tend not to add much formatting at the table stage. I prefer to leave raw data alone there and use formatting mainly in queries, forms, and reports where it's needed for display purposes. For example, you might store an order date and time when an order is placed, but on customer invoices, you might only display the date and not the time. At the same time, you can use a different report for management that includes both the date and the exact time to help analyze sales trends throughout the day. This flexibility lets you apply various formats to the same data field depending on the audience and purpose.
Access provides several predefined formats such as General Date, Long Date, Medium Date, and Short Date, but you can also create custom formats using specific characters or symbols. Text formats, for example, often make use of the at symbol to indicate where a character should be displayed. You might want to store phone numbers as digits only, but then apply a format to present them as (123) 456-7890 for readability, where each placeholder and symbol reflects how the field should appear.
Each data type comes with its own set of formatting characters. With dates, you have codes for year, month, day, hour, minute, and second, using specific letters. For instance, "n" is used for minutes since "m" is reserved for months. You can write custom date formats that combine these codes in different ways according to your needs.
Numbers and currency fields also offer predefined and custom formats. The General Number format displays values as they are, while Currency format includes the appropriate currency symbol as well as commas and two decimal places. These defaults can be adjusted based on your regional settings in Windows. Additional formats include Euro, Fixed (which specifies the exact number of decimal places), Standard, Percent, and Scientific. While custom number and currency formats are available, I generally find myself customizing date formats more frequently.
Yes/No fields can be presented as Yes/No, True/False, or On/Off based on the context. You can adjust this to best fit your display needs.
Sometimes, especially with calculated fields or more complex queries, setting the format in the property sheet does not always produce the desired result. In those situations, the Format Function is extremely helpful. By wrapping your calculated field in the Format Function and specifying the desired display type, such as Currency or a custom date format, you can ensure that your results always appear as intended. This approach is especially useful when dealing with nested or aggregate queries, where the property sheet's format might not work reliably.
The Format Function is available in VBA as well. For instance, you can use VBA code to apply specific formats to different fields dynamically. Let's say you operate in both France and the United States and you want to display the Euro symbol for French customers and the Dollar sign for everyone else. You can use VBA to adjust the currency format based on country, updating the display every time you move to a different record.
If you would like to study these concepts in greater detail, I cover the Format Property in depth in my Access Beginner Level 3 course. Expert Level 3 focuses even further on custom date formats, while Expert Level 8 discusses currency formatting and applying formats to query columns. Other expert-level courses go deeper into various formatting scenarios, using the Format Function, and handling these tasks in VBA programming.
This explanation should give you a solid overview of how to use the Format Property and Format Function in Access to control the display of your data. If you have any questions, feel free to leave a comment. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Understanding the Format Property in Access
Changing display formats in tables, queries, forms, and reports
How Windows Regional Settings affect date formats
Setting the Format Property in table Design View
Setting the Format Property in query Property Sheet
Setting the Format Property in forms and reports Property Sheet
Effect of table-level formatting on new versus existing forms
Best practices for applying format at various database levels
Using different formats for the same field in different objects
Predefined Access date formats overview
Creating custom date formats with special characters
Custom text field formatting with the at sign and literal characters
Custom number and currency format options
Formatting yes/no fields as Yes/No, On/Off, or True/False
Issues with Format Property not applying to calculated fields
Using the Format function when the Format Property does not work
Format function syntax for currency and dates
Using the Format function in VBA for dynamic formatting
Dynamically changing format based on data (e.g., country-specific currency)
Setting format dynamically in VBA Form Current event
|
