ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 2010 Beginner Level 3 Tutorial Table Field Properties, Field Size, Caption, Format, Input Mask, Validation Rule, Default Value
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Beginner 2

Next: Access Beginner 4 >

Access Beginner Level 3
Beginner Microsoft Access Tutorial - 1 Hour, 19 Minutes

 
 
This Microsoft Access video tutorial picks up where Level 2 left off. This class focuses on a very important topic that is crucial to proper table design in Access: Field Properties for table fields. You will learn:
 
  - What are Field Properties
  - Field Size
  - Caption & Description
  - Format Property
  -
Decimal Places
  - Input Masks
  - Setting a Default Value
  - Validation Rule & Text
 
- Using Inequalities

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first lesson of this course (free of charge), or scroll down for more information.




 

Access 2010 Beginner Level 3
Description: Access 2010 Beginner Level 3
Versions: Microsoft Office Access 2010
Access 2007 users should be able to follow along fine
Pre-Requisites: Access 2010 Beginner Level 2
Running Time: 1 Hour, 19 Minutes
Cost: $9.99


This class picks up where Level 2 left off. We will focus on the Field Properties for your table fields. You will learn about the most popular properties, including field size, caption, description, format, decimal places, input masks, default value, validation rule, validation text, and using inequalities.

We will begin by learning about the Field Size property. You will learn about field sizes for autonumbers, text, and number fields. You'll learn about the different number types and which ones you should use: byte, integer, long integer, single, double, and decimal. We'll talk briefly about Replication IDs and why you don't need them.

 

Next you will learn about the caption and description properties, why they're useful, what they do, and what the difference between the two are. You'll learn how to change the column header in datasheet view and how to set a status in the status bar at the bottom of a form.

 

You will learn about the format property, and how to set formats for several different data types including numbers, text, currency, and dates. You will learn about the different number type formats including general, currency, fixed, standard, percent, and scientific. You will learn about the decimal places property and currency formats. You will learn how to format dates and times. You'll learn about the stock time/date formats: general date, long date, medium date, short date, long time, medium time, and short time. You'll also learn how to create your own custom date/time formats, like yyyy-mm-dd. You'll learn how to format text values like phone numbers and Social Security numbers.

 

You will learn how to work with Input Masks to control the way data is entered into a field. We'll talk about placeholder characters and whether or not you want to store them in your table. We'll see many of the popular stock input masks such as phone number, zip code, password, and more.

 

You will learn about the default value property and how you can use it to create the starting value for any field. For example, if most of your customers are from New York, you can set it as the default value. You will learn how to automatically set the current date and/or time for a new record using the Date() and Now() functions.
 

 

You�ll learn about the validation rule property which allows you to specify the values that can go into fields. For example, if you want to make it so that a customer never gets assigned a credit limit more than $5000, you can do that with a validation rule. You'll learn about the validation text, which is a message you can provide if the rule is violated. We'll talk about what happens when existing data violates the validation rule. You'll learn about logical inequalities (greater than, less than, not equal to, etc.) and the keywords you can use with them (AND, OR, & BETWEEN). You'll see how you can use the Date() function in the validation rule.

 

This is the perfect class for anyone who is wants to learn proper table design in Microsoft Access. Understanding field properties is crucial. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Beginner Level 3

0. Intro (6:53)

1. Field Properties 1 (14:40)
Design View
Field Size
AutoNumber Field Size
What is a Replication ID
Text Field Size
Number Field Size
Byte, Integer, Long Integer
Single, Double, Decimal
Warning: data may be lost

2. Field Properties 2 (5:13)
Caption
Changes default form label
Changes column header in datasheet
Don't confuse with Description

3. Field Properties 3 (16:02)
Format Property
Number Formats
General, Currency, Euro
Fixed, Standard, Percent
Scientific
With percent 1 = 100%
Decimal Places
Rounded values displayed
Actual values stored
Currency Formats
Format might hide data
mm dd yyyy, hh nn ss
F1 for Access Help
List of Date Format Codes
Text Format
Phone Numbers
@@@-@@@-@@@@
Yes/No Format
4. Field Properties 4 (5:25)
Input Mask Phone Number
Placeholder characters
Store symbols in the mask?
Enter data
Warning message on invalid data
Date/Time Input Masks
No Wizard for numbers/currency
Help for Input Masks

5. Field Properties 5 (9:47)
Default Value
State of NY
NumEmployees 1
DiscountRate .05
Automatically set current date
Date() Function
Now() Function includes time
Yes/No Default Values

6. Field Properties (16:16)
Validation Rule
Inequality Symbols
NULL Values not the same as ZERO
Existing data violates rule
Validation Text
ESC escape to cancel editing
Range of values
AND OR
BETWEEN keyword
Date Values #1/1/1980#
Date() Function in Validation Rule
Functions in Validation Text

7. Review (4:47)

 


 
Keywords: Table Field Properties, Field Size, Caption, Format, Input Mask, Validation Rule, Default Value, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, replication id, byte, integer, long integer, single, double, decimal, description, validation text, number format, general, currency, fixed, standard, scientific, percent, decimal places, date formats, access help, date format codes, text format, phone number, placeholder characters, store symbols, set current date, date() function, now() function, inequality, null values, AND, OR, BETWEEN, date values in # symbols
 
 

Student Interaction: Access 2010 Beginner 3

Richard on 5/21/2012:  This Microsoft Access video tutorial picks up where Level 2 left off. This class focuses primarily on table field properties in Access. You will learn: - What are Field Properties - Field Size - Caption & Description - Format Property - Decimal Places - Input Masks - Setting a Default Value - Validation Rule & Text - Using Inequalities Click here for more information on Access 2010 Beginner Level 3, including a course outline, sample videos, and more.
Richard R on 5/21/2012: Now that you're finished with this class, please take a few short minutes to complete our STUDENT SURVEY and let us know what you thought of the lessons!
 david on 5/23/2012: the F1 key does not bring up the help screens when I press it on my computer

Reply from Richard Rost:

They're context sensitive, so it all depends on WHERE your cursor is when you click on F1. Also, it is possible if you didn't perform a FULL install of Office that you don't have the Help files on your computer. Rare, but possible.

 david on 5/23/2012: when I press escape once it reverts the whole record back to what it was before I do not have to hit the ESC key twice. Do we have different releases of Access 10?

Reply from Richard Rost:

This is how Access behaves on both computers I have Office 2010 installed on. I do have the same version on both. Does anyone else experience different behavior? I hit ESC once to abort entry in the current FIELD, then a second ESC press aborts editing of the entire record.

 Bruce on 5/24/2012: Field Properties module 1 stops at the time index of 8 minutes and 19 seconds. I will re-try in Firefox. I am currently in Chrome.

Reply from Richard Rost:

The video isn't cut off. The problem is that the FLASH PLAYER that I'm using is garbage. It's ticking off seconds too fast. Not to worry... I just purchased a NEW video player that I'll be incorporating into the web site over the next couple of days. In the mean time, as long as you see the AccessLearningZone.com logo at the end of the video, it's done.

Bruce Reynolds on 5/24/2012: Module 6 stopped at time index 11:24. Will go into Chrome and retry. If it happens in Chrome, will try Opera. If it happens in Opera, will try IE9.

Reply from Richard Rost:

Same problem. Happens for me too. The old Flash player doesn't like the NEW videos... will be fixed soon. The whole video IS playing though.

 Maurice on 6/3/2012: Richard,

I have my own preferences for how a form should look so my question is can I create my own theme that can be saved so whenever I need to create a form my preferred fonts, colours etc are already set?

Kind regards

Maurice

Masroor Sarwar on 6/6/2012: Hi Richard,
what is the scheduled date of release for Access 2010 Beginner 4 and 5.

thank you,
masroor

Reply from Richard Rost:

Working on Access 4 today. I hope to have it released tonight. 5 should be another day or two. My goal is to get them both out this week so I can start the Searching/Sorting Seminar this weekend.

Masroor on 6/16/2012: Hi Richard,
Can you please tell me the final release date for Access 2010 beginner 4 and 5. I would like to finish this class as soon as possible.

thank you,
masroor

Reply from Richard Rost:

I'm working on them. Probably another day or two for Level 4.

JOHNBOSCO GYABAA on 6/19/2012: it is very good
  on 6/20/2012: In the UK we have a Disability Discrimination Act and the description ought to be used as a person suffering from disability might find the description useful.
Edward Clancy on 6/20/2012: Can autonumber be forced to start at a number other than 1, lets say 1001.
Beginner 3 Field Properties 5 (00:00)-(00:37)

Reply from Richard Rost:

Yes, but it involves a trick. Just insert as many BLANK RECORDS as you want (in your case, 1000) and then add your first real record. It will have ID 1001. Now you can go back and delete the first 1000 blank records.

Yes, you can do this automatically with a macro, SQL, or some VBA programming, but that's beyond the scope of my beginner lessons.

Edward C on 6/20/2012: Thank you Richard, another DB I was working on gave the option to give a start number

Reply from Richard Rost:

Yes that's just the quick trick. Like I said you can create a macro or some programming to do this with a loop so you don't have to sit there entering 1000 records, but for beginner users just create a bunch of records, copy and paste them, and that's easy to do.

Richard on 6/21/2012: IMPORTANT: There is an ERROR in the video. A Long Integer is +/- 2 BILLION not million. I do say "BILLION" in the video, but the slide is wrong - it only shows 2,000,000. The actual range of a Long Integer is −2,147,483,648 to 2,147,483,647 which is just fine for numbers you're going to use on a daily basis that don't need a floating point. Sorry for the mistake. Thanks to Micah M. for pointing this out.
 Lynn on 6/23/2012: I am working on my own project.
I need to scan a small document into
each record.How can I scan into attachment in one step or
automate it? I assume this is best thing to do.
Thank you, Lynn J Robbins

Reply from Richard Rost:

Lynn, I've been asked this question before. I don't really recommend scanning documents DIRECTLY into Access. However, most scanners have a feature where you can scan and save to file images (JPG or whatever). If you take all of your documents and scan them to a folder, then you can use the techniques I show in my Access Imaging Seminar to just go through and pick them, one at a time, and save the location (full path and filename) in your database. After that point, users can just click on your link to open the document. No need to save the actual documents in your database. That makes your database big and inefficient.

Miguel Cortez on 6/24/2012: I can't open your video player, Real player appears
Instead

Reply from Richard Rost:

Download and install the VLC Media Player. Make sure it is associated with MP4 video files on your system.

michael j on 7/2/2012: i get an error message when i try to use wizard help me please?

Reply from Richard Rost:

Michael, I need a little more detail. Can you tell me EXACTLY what you're doing, and EXACTLY what the error message says? There are a lot of wizards in Access.

Leslie on 10/22/2012: validation rule on date:
I want a date lets say 1/1/12 + 90. I want it to tell me what is going to be the date 90 days after 1/1/12, so it will print a report?

Christine Lucas on 11/21/2012: The lesson finished after Validation Rule & Text was demonstrated was there meant to be something about "Required" and "more..."

Reply from Richard Rost:

I'm not following you. Are you saying there's something missing?

Raul Pendas on 12/7/2012: How you can make a field to calculate?
I would like to make a invoice.
Thank you for your attention to this matter.
Raul F. Pendas

Reply from Richard Rost:

Raul, see this tutorial on Calculated Query Fields.

Invoicing is covered in my Access 300-series, starting with Access 301.

Sereatha Berry on 12/14/2012: How do i create a drop list under a tab where the customers can click on choices?
For instance, I want to create a tab for Areas of Interest, and when they click on the space a drop box appears with choices that they can check off with more than one answer.

Reply from Richard Rost:

This is way beyond Access Level 3. I start covering simple drop-down lists (called COMBO BOXES) in Access 8.

There is a way to select multiple items using a LOOKUP WIZARD in Access 2010, but I personally don't recommend them. I'll be covering them soon... probably in Access Expert 2 or 3.

The PROPER way to handle selecting multiple items involves using a MULTI-SELECT LIST BOX which I cover in Access 321, but that involves some VBA programming.

Nick P on 12/30/2012: Is it best to format tables or would it be better to do all formatting in forms and reports?

Reply from Richard Rost:

That depends on the situation. If you know you're always going to use a specific format (like phone numbers or SSNs) then feel free to put the formatting in the table. If it's something you might only use in a specific situation, put the format in your form or report. Formatting in a form/report always overrides the table formatting so you can still change it on the fly if you want to.

Nick WILLIAMS on 1/6/2013: What to think about when deciding between format or input mask?

Reply from Richard Rost:

Do you want to control how the data is input, or just how it's displayed?

Robert Maddox on 1/17/2013: Is Field Size storage actually based on the actual # of characters entered or by the defined field size (i.e.- a 30 character name or the 255 character definition)?

Reply from Richard Rost:

In the old days, when you specified 30 characters for a text field, Access reserved 30 bytes of storage for EACH record. This was a waste for something like FirstName when you only used, on average, 7 or 8 characters for each record. However, newer versions of Access don't do that anymore. They dynamically allocate the space per record. Furthermore, when you compact/repair your database, Access gets rid of any empty space for all of your fields (including Memo fields which are notoriously bad at bloating your database). Now, the field size is more for YOUR use - to limit the maximum size of the field.

Joe on 2/6/2013: Is there a way to make the last item entered in a field in a form to become the default value until changed to something else? I often enter data that has 5 or 6 fields the same for 10 or 12 entries of 1 item then I change most of the fields to something else. It would be nice to not have to enter the same data in every field over and over again.
Jennifer Hull on 2/26/2013: Again thank you so much . Am enjoying this. Sorry for length of queries but am trying to sort this database asap. 1. When I started with a data base of medical results, ( with access 2000) I battled with the number and decimal value fields.I was eventually advised to set all as currency as there was no problem in enterring the decimals or doing calculations. Is this still acceptable.Have used this format with the new database.
2. Null values. I was advised to use a default value by a statistician to have a default value that equated to " no data available" I set this as -9 as the database at that time set 0 automatically as default and I used 0 as a value and occasionally used '-' values up to -3. Is this still acceptable or is it now possible to use null value in number fields that will be used for calculations. I understand from what you have just said that I may be able to replace the -9 with a blank value with a query later on. Thank you so much again excellent series

Reply from Richard Rost:

1. Use currency for DOLLAR values. Otherwise use doubles for floating point numbers or long integers for everything else... when in doubt.

2. I would use NULL for "has no value." You can replace all of your negative numbers with an UPDATE QUERY.

Jennifer Hull on 2/26/2013: Have asked my questions and love your course. May I make a suggestion? Is it possible to add the topics covered in each lesson to the list of lessons? This would make it easy to go back to look again at a specific point.

Reply from Richard Rost:

There's a comprehensive index HERE.

John Mills on 2/26/2013: As identified by Christine, the lesson did not cover the "Required" property although it is mentioned as covered in the lesson review.

Reply from Richard Rost:

Did I really skip it completely? Or did I just mention it and forget to go over it in detail? I seem to remember going over it. In any case, it's quite a simple property. If you set REQUIRED to YES then that field MUST have a value. The user cannot leave it blank.

Jennifer Hull on 2/27/2013: Thank you so much. Exactly what I wanted. C
Jennifer on 2/27/2013: Thank you.
Yvonne Foord on 3/7/2013: In the Beginner 3 review you say you covered the 'Required' field, but it's actually covered in beginner 4.

Reply from Richard Rost:

Thanks, Yvonne.

Vincent D on 3/15/2013: at the 13:02 mark you refer to the pound sign but what im looking at on screen is the hash mark "#"


Reply from Richard Rost:

Yes, here in the States the hash mark is also called the Pound Sign #

Vincent Dyas on 3/18/2013: do memo fields with their memory capacity slow down the running speed of the data base ? if so should thy be used sparingly ?


Reply from Richard Rost:

Memo fields are OK if used properly. Don't OVERuse them. If you can get away with using a standard text field, then you should. There are benefits (you can't sort a memo field, for example).

Patricia C on 4/5/2013: The NOW() function is not returning time with the date. I am using Access 2007. What am I doing wrong?

Reply from Richard Rost:

Where are you using it? In the default value property of a field? In a query? Is your system clock set?

Patricia C on 4/5/2013: Sorry,forgot to mention that I assume my system clock is set because time displays in the lower right corner of my screen.
Tricia Clark on 4/7/2013: I am in Access. The function now() is in Defautl value for CustomerSince Date/Time field per the class instruction.

Reply from Richard Rost:

Does your format show time? If you have the "Short Date" format, for example, it will only show mm/dd/yyyy.

Patricia on 4/8/2013: That's so logical! Thanks.

Reply from Richard Rost:

Just call me Mr. Spock.

Daisy O on 4/11/2013: Dear Richard
Based on my experienced of developing Access Programming, ESC is a complete taboo key to use. I do not use it as it can never help but to make the thing worst.

Back to you.

Reply from Richard Rost:

I pretty much only use it to quit data entry. If I'm entering a change and realize I don't want to continue, ESC cancels everything nicely.

Laurie Jones on 5/23/2013: How would I have a query or form automatically update a date/time field so it shows the current date/time in the header or footer?

Reply from Richard Rost:

If this is something you need to happen in real time, you'd need to use the OnTimer Event.

Maggie on 6/10/2013: How would I set the validate rule for a End Date input so an error message will prompt out when we wrongly input a date in "End Date" which is earlier than the date in "Start Date". Thanks

Reply from Richard Rost:

You can't do this in a field-level validation rule. You have to use a TABLE-LEVEL validation rule, which I cover in Part 3 of my SQL Seminar. It's a little more advanced. Open the properties for the TABLE and set the validation rule for the TABLE to be:

[EndDate]>[StartDate]

SYED KHURRAM on 6/15/2013: I am making a order form for my store i have it on excel sheet have my store logo
and have terms and conditions in text the text is about more than half the page the problem I am facing is each time my employees open it they have data from previous customers how can i import that order form from excel to access

Reply from Richard Rost:

Setting up an order form is a bit complicated. I cover it in my Access 301 class. To do it RIGHT involves multiple tables. I'll be showing a simpler version in my next couple of Expert classes.

Syed Khurram on 6/15/2013: Thanks for the reply I will appreciate if you inform me whenever it is available.
Sandy Reikofski on 6/28/2013: In the default value for state, can you some how make it so when you type in the first or second letter it will automatically fill in based in on certain choices?


Reply from Richard Rost:

I would recommend a Combo Box for that. We'll get to that Access Beginner 8.

Sandy Reikofski on 7/10/2013: My database will have names of churches, other businesses and personal names. Would you recommend me making a separate table for each of these or is it okay to have them all in the same table?

Reply from Richard Rost:

That's up to you. I generally recommend keeping them altogether in the same table. You can have OrganizationName (either a church or business) and then the FirstName and LastName of your contact. If you have multiple contacts, I teach you how to put them in a separate related table once you get to the Expert lessons.

Sandy on 7/10/2013: Thank you very much!
Martie Jacobs on 7/24/2013: Richard, thank you very much for the support added to the training. Being new to Access, to find an answer takes hours as I do not yet know all the terms. So, it is such a relief to talk to a person on the other side who knows what I mean even if I use the wrong terms. Your training and support is of invaluable help and much appreciated. Martie

Reply from Richard Rost:

You're very welcome. I do my best to provide answers to questions where possible here in the Forums. I try to answer everyone who has a question with a short answer for FREE. If I can answer you in 30 seconds or less (which is most questions) I don't mind helping you out, or at least pointing you to the right lesson if it's something I've already covered. For those people who need more in-depth assistance, the TECHHELP page is also available. I don't LIKE having to charge folks for support... but alas there are only so many hours in the day.

Janet G on 9/11/2013: I have an existing Db that when I enter Date()in default value, it says it doesn't recognize the function Date. I tried making a new table within this same db and tried it - same thing. I open up PCResale and it works no problem. My db began in Access 2003 and the data resides on a server.

Reply from Richard Rost:

That's odd. Have you tried creating a NEW database? Make sure you have:

=DATE()

And not just Date() or =Date in the field. Either SHOULD work, but the official command is =Date()

If none of that works, your older database may have a reference to a library missing, which is a pain to fix. Make a NEW blank database and then import the objects from the old one into the new shell.


Michelle Rhymer on 10/16/2013: Hello, I have entered the @@@-@@@-@@@@ on the phone number in the sample database and it not change the format when I switch to datasheet view.
Jim Ogier on 12/17/2013: Is it posible to display numbers as Fractions? Can I enter a number into a field as a decimal with three places such as "0.625" and have it displayed as 5/8 or " and "2.0313" and display 2 5/16?

Jim O

MICHAEL J on 1/25/2014: Is it possible to have 2 autonumber fields? I have 1 which is for the id (pk) but I would like to second autonumber to use as a receipt number

Reply from Richard Rost:

Yes, you can have two Autonumbers, but it sounds to me like you want one Autonumber ID and a second field (probably Text) where you can enter in your own receipt number.

MICHAEL J on 1/26/2014: Thanks for your reply.At the moment I am using 2 fields as you suggested, my concern was that the user would have to remember the last number used or look it up. I tried to create a second autonumber but got a message that Access only allows 1 autonumber for each table. I have got round the problem for now by using the ID autonumber for the receipt number. Maybe later it the course I will see a better way.
Sonia Ferreira on 1/29/2014: Mobile phones in Brasil have 8 OR 9 numbers, depending on the city. Is there a way to leave an optional space in the input mask or have different input masks depending on the city? This is still rather new here, no one seems to even know the format but what I use is
9 9999-9999 or 9999-9999.

Jennifer Nikolsky on 3/6/2014: Hi, I am in Access three, I cant seem to delete a row because I have entered fields for default that include automatically on mailing list and NY as default state and 5% discount rate. I have Access 7. Thanks!
Jennifer N on 3/13/2014: Hi Richard, never mind my question, I just went over your lesson again and found what I had done wrong. Thanks!




Marcia Hall on 4/21/2014: On my form under Num Employees, the numbers entered are not showing on the form only #NAME? What have I done wrong? Also, since I shortened the fields, Discount Rate is no longer tranferring. I am using Access 2013.
Miguel on 4/28/2014: Why after I set up the 'customer since' field in general date in the designed view it gives me a lot of number signs on my customer table. Thanks in advance
Joe Beniacar on 5/12/2014: Hi Richard,
Is there ever a time when you would want to apply an Input Mask AND a Format to a field? (or is an Input Mask just a more detailed version of the Format, so we should just use this?)
Thank you.

Reply from Richard Rost:

My general rule of thumb is that the Input Mask is for data entry, and the Format is for display. Format is easier, so if you just care about how it LOOKS, stick with that. If you want to control how data is ENTERED (and looks) then go with an Input Mask. Can you use them both? Sure.


Ernest Palacios on 5/20/2014: Access 2010 Beginner 3, part 4, Lesson 4, Field Properties time code 02:36: When I try to apply the input mask for the phone number design view, I save the table and then switch to Datesheet view but when I look the the Phone Number it still show a contin
Ernest Palacios on 5/20/2014: Access 2010 Beginner 3, lesson 4, part 4 input Masks, video time 02:36. When I apply the input mask for the Phone number in design view, I save the table and the go to datasheet view, but the mask doesn t show unless I click on each individual number, otherwise I only see one continuous number. What am I doing wrong?

Regards,

Ernest (Mike) Palacios

Dan Kramer on 6/3/2014: I added an input mask for a telephone number (!\(999") "000\-0000;0;_) but my existing numbers display without the mask in table view. What am I doing wrong?

Thanks!

Mark Etherton on 6/15/2014: Hello Richard. Does the "Expert Series" coverage of custom date fields cover how to use and display both known dates and approximate dates e.g "Circa" or "C" in the same field. Thankyou I am enjoying the lessons and they are the best I have ever seen.

Reply from Richard Rost:

What do you mean by "custom" date fields?

No, you can't put "circa" in a date field. The best you could do would be to have a second field (perhaps a yes/no value) that indicated whether or not the date was "circa" and then DISPLAY it with the "c." in a custom query/form field.


Raymond Trussell on 6/30/2014: Why when using input mask do the input just ding and not give message
Susan Holbrook on 8/7/2014: I have created a form from a customer table. There is an auto number to identify each customer. I would like the users to be able to type in new customer details and have these automatically allocated with the next auto number. How can I do this?

Classes are fantastic - I am just so impatient to be able to make this do all the things I want it to do...

Thank you

Sue

Reply from Richard Rost:

When you go to a new record and start typing in data, the next Autonumber will automatically be assigned.

Jafar Abdalla on 8/19/2014: Access is showing the wrong date when I use date()?

Reply from Richard Rost:

Check your system date. Access gets this value from Windows.

Chris Thompson on 8/25/2014: Jafar,

The time and Date is read from your Computer - I am sure you have checked for the correct time and date.

What are you getting as an example of "the wrong date". Is this within VBA code or in a field within a form or report?

If you are using any kind of format statement, make sure you are using the correct format. For more details check out: http://office.microsoft.com/en-ca/access-help/format-function-HA001228839.aspx

As well, I too had issues with the date function a while back and it was because I was using a non-USA locale (Canada English) where we show our date as DD/MMM/YYYY. However, the US locale displays info as MMM/DD/YYYY. I believe, if I recall correctly, I could not resolve this until I set my PC for US Locale in the control panel under "regional and language options" - make note of your setting before you make any changes.

Hopefully this helps.

Megan Prosser on 9/8/2014: I cant change the Field Size on my customer form. The form will not allow me to change the number. Help?

Reply from Richard Rost:

Field Size is a TABLE field property.

Joseph Beniacar on 9/17/2014: Hi Richard,
Would the DATE() function update the cell with the new date "each" time you close it down and reopen it another day?

Reply from Richard Rost:

For NEW, blank records, YES. Existing records stay where they are.

malcolm Wills on 10/4/2014: If you use Date() as invoice date wouldn't it change each time you looked at the invoice. is there any way to make date() non- volatile?

Reply from Alexander Hedley:

Hi Malcolm,
If you set your Default Value to Date() this is only for NEW records. Once the record is saved it won't be updated the next time you go to it.

a on 10/21/2014: at around 11:20 + the list I get has only 2 options and it's not the same page you are puling up with F1. Can you post a direct link here. Neither of the choices I have lead to where you are going. Thank you kindly.

Reply from Alexander Hedley:

Hi A,
Which list is it you mention, I've checked the time but Rich is choosing a Date Format.

faye primeau on 2/10/2015: I had a question today at work and had skipped this lesson section. Today I thought I would check and sure enuf, my answer was in it...you had covered it Richard. Had trouble with setting up a percent field in a database. Thank you!
Jeffery Giocondo on 2/19/2015: I've just started learning Access using your program and I'm on Beginner Level 3 and was wondering when/if you will show us how to create a switchboard? Also, we so far have covered how to create a table and wondered if one could have more than one table in a database? I'm thinking the answer is yes for sure.

Reply from Alex Hedley:

You can't make them anymore and I wouldn't advise them anyway, it's better to just create your own Main Menu form with buttons to open other Forms.

Yes you can have multiple Tables, if it's not been covered yet it will be soon.

Daniel Johnson on 2/27/2015: ACCESS 2013 does not display the description field


Reply from Alex Hedley:

Which Description Field and where?

Kenny Nelson on 3/3/2015: Is there a way to access field properties that are not listed on the properties sheet? For example, I have an ActiveX Barcode Control that has several tabs of properties that I can access by right-clicking on the barcode text box. I would like to be able to use the Licensee property in VBA code to determine which of our locations the user is at.

Reply from Alex Hedley:

Where did you get this control from?
Is there no documentation with the control?
If you set an object in your code does intellisense show any when you use the dot notation to get a property?

Kenny N on 3/4/2015: I have since been informed by the barcode provider that Licensee info is not accessible by code due to information sensitivity. As a general question, though, are all object properties that are accessible by code listed on the properties sheet, or are there "hidden" properties?

Reply from Alex Hedley:

Would depend how it was coded but most of them should be.

Karen Acosta on 3/18/2015: My question is about the description. When I enter a description in the table design it does NOT display when I click in the field to enter data. I am running Access 2013 so not sure if that is a change but I like the feature. I even deleted my form and recreated after table descriptions were entered.

Reply from Alex Hedley:

So Description is not showing along the bottom of the Form?


Tyson on 3/19/2015: Full version Access 13' - F1 pulling up a web page after hitting F1 while the Input Mask box is active.
"Hi there! You have landed on one of our F1 Help redirector pages. Please select the topic you were looking for below.
TextBox.InputMask Property (Access)
ComboBox.InputMask Property (Access)"

Reply from Alex Hedley:

Sorry, I don't understand the question.

Tyson Y on 3/19/2015: In Access 2010 Beginner 3, Lesson 4, Richard mentions hitting F1 in the Input Mask box to bring up the help system. When I hit F1, it does not take me to the help system. It brings up a web page that states:
"Hi there! You have landed on one of our F1 Help redirector pages. Please select the topic you were looking for below.
TextBox.InputMask Property (Access)
ComboBox.InputMask Property (Access)"

How do I bring up the help system that he is referring to? Richard replies to another user in the forum that the full version of Office may not be installed or that the user is not hitting F1 in the Input Mask box. I am not sure if I have the full version or not, but thought I did.

Reply from Alex Hedley:

Is there the ? help button anywhere in the app or Help from the Toolbar?

Did you install access on the system yourself?

Brenda Peterson on 5/15/2015: Around 4:39 you said to press f1 and it does not take me to the TextBox.InputMask Property. What is another way to get to this information?

Reply from Alex Hedley:

You can open up the Property Sheet by double clicking where the rulers meet in Design View.
You can then select the Textbox and go to the Input Mask Property from the List.

VIJAY KUMAR on 6/4/2015: Is there any method to change currency symbol other than dollar and Euro in currency field type
Thanks

Reply from Alex Hedley:

This will be what your region settings and languages will be, change it in Control Panel.

Ishaq Khan on 7/4/2015: Mr. Rost how are you? Most of my customers phone number starts with area code 916. Is there any way to make (916) as a default value of the are code in parentheses?
Thanks

Reply from Alex Hedley:

Is this a text field with an input mask?

Lesley Lockie on 7/17/2015: Hi Richard, enjoyed your 2013 beginner courses. I have a question but have skipped to Navigation buttons, thrown myself in. I have a home page/main menu of buttons, which go through to different forms of more navigation buttons which then take user to the form of records they want, i.e. Inv Mgmt to Audio Visuals to the Form based off a query of Inv Mgmt Table/AV query. When they are clicking on all the buttons is it closing the forms automatically as they go, i.e. if they click on 10 buttons are 10 different forms/tabs open but you just can't see them as I've turned off tabs/panes etc.

Reply from Alex Hedley:

Are you using Overlapping Windows instead of Tabbed Panes?

Katja Braun on 7/23/2015: Hi Richard, greetings from Germany! Is there a way to upgrade my form with later added descriptions? I might have learned that users ask for explanations. If I have to do the formating again, it is really tiresome.


Reply from Alex Hedley:

Are you wanting to add further data to a field that already exists or add a brand new field to your table?

Katja Braun on 7/23/2015: And by the way, I use a German ACCESS 2013 version and your classes are extremly helpful. I learn a lot, thank you for your efforts!
Katja Braun on 7/28/2015: Hello Alex, I want to add descriptions to a field that already exists.

Reply from Alex Hedley:

In Table View there is a Description column where you can add information that will show up in the Status Bar.

Hazim Rahbi on 8/4/2015: Hi Richard
Where would i find information on how to have a multiple choice 1-5 answer. i.e for example 1 for a particular answer, 2 for a different answer and so on

Thanks

Reply from Alex Hedley:

The Web Database Seminar shows how to create a Quiz engine, would that be of use?

Philip Bartlett on 9/8/2015: How do I blind all but last four in SSN

Reply from Alex Hedley:

Why not just use a Right(FIELD,4)
Then just show that in your Form/Report
You could do "***-**" & Right(SSN,4) if you wanted to make it appear blinded.
Make this a calculated Field in your Query.

Joe Beniacar on 9/12/2015: Hi Richard, is there a way to allow the Validation Rule property to accept Null values? If someone enters in a default value to remove the blanks, might it just get lumped in with the rest of the Table's data as "known" data (rather than data you would like to have checked) and then your Table winds up with some "bad data" (which is worse than "no data"), just to get the Validation Rule to work? Thanks a lot!

Reply from Alex Hedley:

You can add OR Is Null to your rule

Joe Beniacar on 9/12/2015: Regarding Philip Bartlett's question about "blinding all but last four #'s in the SSN's", could you make the concatenation of *'s and RIGHT the SSN field's Format field property in the Table/Form, as well? Thanks!
Mark E on 9/16/2015: Hello Richard - I have been a while getting back to this item. Continuing from above. If I want to sort by "date" (e.g for photos) and have two types of date formats some with just the approximate "year" known and others with the "day/month/year" how might this be done.

Reply from Alex Hedley:

Are you storing this as a Date Field or Text?

Mark E on 9/19/2015: Thanks for the prompt reply. I am looking for help on an appropriate way to set up the data base to enter and sort the two different types of dates for photographs.e.g "c1990" or "26 March 2010". I assume Access does not allow two different date formats to be used in a single date field.

Reply from Alex Hedley:

You can only have one date, you'd need to put in day month and year.
You could have another field that determines what to show, day, month or year and then use that to format the date to only show the year.


Resat Ilgaz Demirel on 11/12/2015: For some reason I am not getting Phone option in my input masks.

Reply from Alex Hedley:

What data type is the Field?

Heather Hudson on 11/19/2015: How can a phone extension be entered?
Also, am I able to manipulate that only some of the numbers have extensions or do I have to show for all?

Reply from Alex Hedley:

You could either have it as another Field or just only add it into 1 field and allow the field a longer character length

R?gis D on 11/25/2015: Hello Richard,
I am trying to do following based on an Access database:
- populate the database based on e txt file being also actually populated itself realtime;
- gnerating graphs directly from the data being generated out of Access database.

Can you tell me if above are possible using Access and, if possible, what course should I take to be able to do so?
Thanks for your help.
Regis

Reply from Alex Hedley:

Importing a Text file is covered in Expert 20
Graphs are covered in a few different videos.
It might be easier to link your db to excel and make the graphs there

Rajiv Dass on 11/28/2015: For Long integer you show "+/_ 2,000,000" but you say "plus or minus 2 Billion (11:15)? which is it?

Reply from Alex Hedley:

Byte — Use for integers that range from 0 to 255. Storage requirement is 1 byte.

Integer — Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes.

Long Integer — Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes.

TIP Use Long Integer when you create a foreign key to relate to another table's AutoNumber primary key field.

Single Use for numeric floating point values that range from -3.4 x 1038 to 3.4 x 1038 and up to seven significant digits. Storage requirement is 4 bytes.

Double Use for numeric floating point values that range from -1.797 x 10308 to 1.797 x 10308 and up to fifteen significant digits. Storage requirement is 8 bytes.

Replication ID Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.

Decimal Use for numeric values that range from -9.999... x 1027 to 9.999... x 1027. Storage requirement is 12 bytes.

MS Article

Lamin Sanyang on 12/23/2015: Hi Richard, am getting along with the lectures quite well and am loving it. I want to ask if it is possible to build formula on Microsoft Access just like it is done on excel? However, if possible but different with the way it is done on excel; kindly let me know.
Thank you!

Rafael Gonzalez on 1/14/2016:
This is what i get when i go F1 key.
Office VBA developer help content

All context-sensitive (F1) developer help for VBA is available on MSDN and GitHub. It is no longer available on Office.com (now support.office.com).

Use the following links to go directly to the VBA help content for your application.

Office 2013 and later VBA references

Access VBA reference
Excel VBA reference
Office VBA language reference
Office VBA Object library reference
Outlook VBA reference
PowerPoint VBA reference
Project VBA reference
Publisher VBA reference
Visio VBA reference
Word VBA reference

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP