Microsoft Access Tutorial 102: Field Properties, Search, Sort, Filter, Parameter Queries, Command Buttons, Combo Boxes, Reports, Compact, Repair

Student Interaction: Microsoft Access 102

Richard on 1/1/2007: Field properties, searching, sorting, filtering, more query tricks, parameters, combo boxes, reports, compact, repair. 129 Minutes.

Richard Rost on 1/1/2001: ATTENTION STUDENTS: The sample database files are no longer installed on your hard drive like it says in the video. Due to size restrictions I've removed them from the installation package and put them on the web site. You can download them from the STUDENT DATABASES PAGE.

candy on 10/13/2007: i find access 101 very useful for me as a starter of using access.. i wanted to learn about the rest of the access tutorial for free but i can't afford to buy the cd. i hope that you'll provide free tutorials for the rest of the course. thank you.. and more power

Richard Rost on 10/13/2007: Candy, I understand your financial concerns. I've tried my best to keep my tutorials as inexpensive as possible (where else can you find an hour of Access training for six bucks?) I do try to provide a lot of free lessons - if you look through the Theater, you'll see that each course has a couple of free lessons on it. Plus, I try to post as many free Tips as I can every week. Obviously, I'm running a business, so I can't give everything away for free - but I've tried to make my courses as affordable as I can (some say they're priced TOO low).

John �Casey on 12/2/2007: I can't get the contents of a list box to sort alpha. When I click on a name in the list box, I get a form with that person�s name which is what I want. The query is sorted ascending but the contents of the list box are sorted by auto number. Can you help?

Richard Rost on 12/9/2007: Yep. We get to this in Access 202 Lesson 3 - sorting the contents of a combo box or list box. A quick solution: design a query that sorts the way you want, and then use that query as the data source for your list box.

Judith�Koester on 12/12/2007: When I click on Combo Box and click on design view, I don't get the wizard, I get the label and box.

Judith Koester on 12/12/2007: On the Reports, Lesson 7, you titled the Report CustomersWithFullAddressQ. Was there a reason it didn't end with an R? Love these classes. Judith

Richard Rost on 1/5/2008: Make sure you have the WIZARDS set to ON on your toolbox. This is the button that looks like a magic wand. If it's not pushed down, none of the wizards will start.

Richard Rost on 1/5/2008: This was a mistake. While running the Wizard I accidentally typed in a "Q" instead of an "R". You're right. Thanks for pointing it out. For everyone else, it happens around time index 10:00. I just didn't catch it at the time.

israel �rivera on 1/14/2008: Hi. If I bought a course without the PDF handbook, but find that I may want to purchase the handbook after all, how may I do so?

Richard Rost on 1/17/2008: Just go to www.599cd.com/HandbooksOnly and you can purchase the handbook from there.

Angela on 4/2/2008: There is a section in this lesson that apparently got skipped. It's only a few seconds, but for beginners, every word can be vital. The time on the video where it omits something is right around 18:47. You are discussing the Yes/No field and then all of a sudden there is a formatting that is displayed on the screen for the phone number field. I did figure out what was omitted, but thought this should be brought to your attention.

Richard Rost on 4/2/2008: Angela, you are correct. Right around 18:47 there is about 10 seconds of video missing. I was showing you how to create an INPUT MASK for a phone number using the characters: @@@-@@@-@@@@. It's visible in the video, but the explanation somehow got cut off accidentally in editing. Next time I update the video, I'll have to make sure to fix that!

salvador�garcia on 4/15/2008: Hi Richard, I have some records in excel files. In order to work such data in Access is there a way to improt such data into an Access Table? tks in advance for you assistance.

Richard Rost on 4/16/2008: Salvador, you can go to FILE > GET EXTERNAL DATA > IMPORT to bring in the information from Excel into a table. I cover this in Access 308.

� on 4/28/2008: How do I find the Table for Access 102 for practice?

Richard Rost on 4/29/2008: All of the files for the Access classes have been moved to the Student Database Files web page.

salvador�garcia on 4/30/2008: Hello I finished Acess 102. I tried to presetn my test however I did not find in the list Acess 102. Is acess 220 the test to take for this level? it looks to me a higher degree. Pls comment. Tks Salvador

sony�cohen on 5/1/2008: I have a field named priceFOB (currency / 2 decimals / standard ) but the system rounds it 15.20 => 15.00 , do you know why ?? any idea where to look at ??

�Chris Morgan on 5/16/2008: When I right click on the picture box I don't get an "insert object" option. When I drag a pictire into the box, it won't show the picture, just the file name. If I click on the file name, I can see the picture in a separate window.

Kevin Dore on 5/18/2008: When I try to insert a picture as an OLE into a form, only the picture file name appears in the box and not the picture. How come ?

John D on 5/20/2008: How do you apply a sort criteria that contradicts another? I have a table of customers and only certain zip codes will be included in a mailing. However, some of these customers are marked VIP - which means, a separate text field includes these three characters. How do I have mail sent to these customers, even though the zip sort has excluded them? Thanks

Richard Rost on 5/24/2008: Salvador, the test for Access 102 is NOT available at this moment. I rebuilt the test-taking portion of the web site a little while back, and I haven't finished putting all of the tests back online yet. I'll post a note here when it's ready. Sorry for the confusion.

Richard Rost on 5/24/2008: Sony, if it's definitely a currency value at 2 decimals, it shouldn't be rounding it like that unless you have some other type of formatting established in your form... is this happening at a TABLE level too or just in your form?

Richard Rost on 5/24/2008: Chris, go to the properties for the box and make sure it's a BOUND OBJECT FRAME. I don't know why you don't see the "Insert Object" option. That should always be available. As far as the picture/filename problem goes, in the properties for the box, make sure that the DISPLAY TYPE property is set to CONTENT.

Richard Rost on 5/24/2008: Kevin, I was just researching this myself, and I found some interesting information about some changes in Access 2003. I don't know if Access 2007 behaves this way too, but here's what I found out: Microsoft used to distribute MS Photo Editor with previous versions of Office (97, 2000, and XP). Apparently, they don't include it with Office 2003. MS Photo Editor is the "OLE Server Application" for images inside of Access. So, basically Access 2003 can't display images inside of forms that are Bound in the table like Access XP and 2000 could. Real smart, Microsoft. Anyhow... if you have an older version of Office, you can install JUST the MS Photo Editor component AFTER installing Access 2003, and that *should* fix the problem - although I haven't tested it myself, so I can't say for sure (this is just what I'm reading on various tech sites). NOW... keep in mind that Access only behaves this way if you try to click and drag a file from the desktop (like I show in the lesson) or you try to Insert Object... the picture. You can, however, still display the picture properly if you COPY AND PASTE it from a graphics application. For example, I just opened a JPG image in my web browser (either Internet Explorer or FireFox) and then I right-clicked on it and selected COPY. Then right-click on your OLE Object Frame in your Access Form and select PASTE. If came in just fine and displayed properly. The same would work just fine if you opened up an image in MS Paint or even something like Photoshop. As long as you're copying and pasting a PICTURE and not a FILE, Access will display it just fine. Very strange... but that's the deal.

Richard Rost on 5/24/2008: John, your SORT won't exclude anyone - just display the records in a different order. What you want are TWO criteria where you say, "include all of these ZIP codes AND all of these VIP customers." I show you how to do something like this in the next lesson (Queries) where you see how to include all customers from NY and all Active customers. Same concept.

Meena�Goel on 6/24/2008: Hi, My PCResale.Net Customer dbs are read only? I got them from the student db files.

Richard Rost on 6/26/2008: If they're set to READ ONLY that's because I pulled them off the production CD. When you burn a CD, the files usually get set to read-only because you can't write back to a CD-ROM. Anyhow, all you have to do is right-click on the file and go to PROPERTIES. Look for the READ-ONLY attribute and check the box off.

Meena on 6/26/2008: Thanks, your courses are great! The specified process worked after I extracted the files. Thank you!

�Kyle on 7/9/2008: Richard, Do you know how I would do a report getting strickly email addresses (with commas after each one) and then export them to a file (like csv file) so that the secratary can cut and past to send many emails at once (like on microsoft outlook). Thanks!

Richard Rost on 7/22/2008: Kyle, at your stage of knowledge (Access 102) I would suggest just copying and pasting your list of email addresses from Access into a text editor like NOTEPAD. Then you could select them in groups of 20 or 30, copy and paste them right into Outlook. Yes, it's possible to come up with a fancier solution, but that would involve some programming.

Brigid�Kowalczyk on 9/11/2008: In my database, i have some of my own records which include IN for Indiana residents. When I tried to put IN for a criteria, Access wouldn't let me. What to do? How do you rename a state? time is 6:30 on the access 102 video.

Richard Rost on 9/12/2008: Brigid, IN is a reserved keyword in Access. You have to make sure you put it inside of QUOTES if you want to use it as a parameter. You really should put all text inside of quotes. Even NY should be ="NY". So make sure instead of just =IN you have ="IN" and you should be fine. I just tried it and it worked OK for me.

Nathan�Wittmann on 9/24/2008: Is it possible to create a criteria that will allow it to recognize part of the cell? For example if I have data that is the following: Clif Bar Is there a way I can use a criteria like "Clif" and allow it find Clif Bar. When I do it now it ignores it because it is not exactly the same. So I want to be able to find just part of the info. I would also like to put in the letter "b" and bring everything that has a "b" in it brought up in the query. Thanks for your help. Also I appreciate all the help in the past. Thanks, Nathan

Richard Rost on 9/27/2008: Nathan, you use the LIKE keyword to show just part of a field. For example, in a query to show anything that begins with "Clif" you put this in your criteria: LIKE "Clif*" To show anything that starts with the letter B you put: LIKE "B*" To show anything that has the word "ball" anywhere in it, you put: LIKE "*ball*" And that will give you "baseball," "football," "ballgame," etc.

�Chuck crawford on 11/7/2008: I have the Access 101 CD and the 102 download but neither seems to have a "help" folder nor the practice database Please advise thanks

Richard Rost on 11/10/2008: All of the database files have been moved to the web site at www.599cd.com/Access/StudentDatabases.

Reilly�John on 11/18/2008: I have a test retest situation. I want to select the highest test score in the series of tests for each student for each student and get a difference score between the highest score and the initial. Then I want to select students that have a difference score greater than or equal to 5 points. I presently have an Access program that compares the students last test score with there initial score. I want to change this to use the students highest score from the field of test scores versus getting the most recent score diference. I have an active Student table with initial scores and Student progress table with the scores achieved at subsequent testing periods.My query selects all of the scores of each client. I want to select the highest. Sorry for the repition but I hope it helps clarity.Any suggestions? Thanks

Gloria�Permint on 12/6/2008: Good evening Richard,I'm using Access 2003.When I click on the command button,I receive a message that says Module not found.When I close the form,the Microsoft Visual Basic window opens and when I click to close a pop up message says This command will stop the Debugger.Can you please explain and help me correct this error. Thanks,Gloria,a long time student and customer.

�Chris Judge on 3/8/2009: Hi Richard, I can't seem to find Tests for Access 102, 103 and 104 on www.599cd.com/tests There is a 101 test (Test 4) which I have taken but Test 5 jumps to Access 220. Have the rest of the 10- tests been removed? Thanks Chris

Richard Rost on 3/9/2009: Chris, I'm working on revising a lot of those tests, therefore many of them are offline. I hope to get them back up soon. Thanks.

�Michael on 7/31/2009: In the Notes field on the form you showed us how to "zoom" the Field so the user can type in a more friendlier way. How do you set the field to automatically do that. IN other words not require the user to hit the combination of shift key...

Richard Rost on 8/3/2009: Michael, this would involve some programming on your part, which we haven't gotten to yet - and certainly is well beyond Access 102. In a nutshell, you'd need to issue the zoom command using VBA code whenever the field is clicked on (the OnClick event) or even make your own zoom button. For my beginner students, I just recommend making the field larger.

Randy�Montalvo on 9/4/2009: Hi Richard. I am using microsoft office 2003 pro, if that makes a big difference. I am having the same as everyone else about the picture not showing up just the file name. I checked the display type and it does say content. What else could be the cause? Thanks in advance for the help.

Randy�Montalvo on 9/4/2009: Sorry Rich forgot to add that it was in Access 102, Video 6, time 13:20.

Dave on 9/14/2009: Hi Richard - In my database, I am thinking of having as one of my fields an OLE box for a map that my field techs can refer to. The map would be drawn on Google maps or some such sources with some notes applied and then printed to an Adobe file. I am guessing my database could get big and slow quickly, as after I downloaded the first map my file size went to 7 MB. Is this going to be a big problem after I have a bunch of records? Also, I noticed I could select a bitmap but not a JPEG.

Richard Rost on 9/16/2009: Dave, yes, saving images in your database can make the tables get very large, very quickly. For your case, you might be better off saving the images as files on your hard drive, and then linking to them, instead of pasting them directly into the tables.

Dave Sterner on 9/16/2009: Thanks, Richard - is the linking to a file instead of inserting an image to be covered in a future course?

Richard Rost on 9/16/2009: Yes. I cover it in Access 203, Lesson 6: Pasting v. Linking Bound OLE Objects.

�Theo on 10/14/2009: Hi, I am looking for a copy of the database for the lessons in Access 102. I do not have a Help folder in C:\Program Files\599CD\Access 102. Is there anywhere I can download it from?

Richard Rost on 10/14/2009: All of the student databases were moved to the STUDENT DATABASES page.

Maks�Kelemina on 10/14/2009: How should I go about to run overdue query

Richard Rost on 10/15/2009: By "overdue query" I assume you mean all orders that are, for example, more than 30 days late? For that just use a simple criteria in your query: DaysLate: Now()-OrderDate This will give you the number of days late. Now for the criteria, just say: >=30 And that should do it. I cover a lot more about this (overdue invoices) in future lessons, especially when we get into the more advanced query types.

Bill�Donegan on 10/21/2009: Richard, I have bothered you already about a problem I am having..Its intermittant...In your class 102 video 5 at approx 7 minutes you are building the new Employees form. You are able to change the background colour using your "format" toolbar.....ever before you insert any fields on form. I can only get the foramt toolbar to work when I have placed a control on form and I have selected that control. Why is that....it does not happen all the time which is frustrating...Im using Access 2000 Pro on Xp Thanks

Richard Rost on 10/21/2009: Bill, I'm not sure. I do have one machine here running Access 2000 on a Windows 2000 machine, and I don't have that problem (in fact, I just tried it again). I will say that Access 2000 is a bit buggy. Make sure you have the latest service packs for AC2000, if you can still get them.

�mahabbas on 11/9/2009: just curiuous regarding interface in Access 2007 as I don't have a similar interface to practice in exactly the same manner. Moreover, the student file downloaded does not open in Access 2007. Certainly the foundation training is similar, but the look and feel, the navigation and enhancements are different.

Richard Rost on 11/11/2009: Mahabbas, you are correct. The foundation concepts are exactly the same, but the interface is completely different. I'll be covering the Access 2007 interface very soon in upcoming lessons.

�Mike on 11/20/2009: Richard, really enjoy the videos and feel that the test hammers home anything I might have missed. Just checking back in on the timing of the Access 102,103, and 104 tests being put back on the test archives. Thanks again!

Richard Rost on 11/25/2009: Mike, I don't know if we'll remake the tests for the old Access classes. We'll most likely have new ones for the Office 2007 classes coming up.

Judith Koester on 12/15/2009: I created a customer table and decided I didn't need some items and deleted a couple of rows. When I created the form, these items show up in for field list and I get a Enter Parameter Value box when I open. How do I get rid of these?

Richard Rost on 12/15/2009: Judith, try not to use the terms "rows" and "columns" when working with Access. Those are spreadsheet terms (like in Excel) and can confuse you in Access. In a database, we have FIELDS (columns) and RECORDS (rows). So did you delete a couple of fields, or records? I'm assuming you mean FIELDS, and the "rows" you're talking about are actually the TABLE DESIGN equivalent for fields (since they look like rows on the screen). Now, if you make a table, then build a form off of that table, then go back and delete FIELDS from the table, you might get the "Enter Parameter Value" box up when you open the form because Access it trying to find fields that no longer exist. You'll have to open the form up in Design View and remove those fields off the form too. Plus, if you have a QUERY between the form and the table, you'll need to clean that up too. Hope this helps.

Judith Koester on 12/15/2009: Yes, I am talking about fields (doesn't help much that ACCESS calls these rows and columns in table design view). The fields don't show up in table design view, but when I am putting together the form and using the Field List the deleted fields show up on the Field List. I'll keep working on it, but am puzzled. Forgot to tell you that I renamed one of the fields rather than deleting it. Could that be a problem? Also am trying to figure out the new "ribbon" on 2007. I'll keep trying.

Richard Rost on 12/15/2009: Once you delete a field from the table, as long as you close everything down and reopen it (table, form, etc.) then those fields SHOULD disappear from the field list. Also make sure you have the latest service pack for Microsoft Office installed. There were some nasty bugs in the first version of Access 2007.

Judith Koester on 12/15/2009: I found out how to get rid of the unwanted fields on field list. I right clicked on field and selected Edit Record Source which opened SQL Statement Query Builder. The weird fields which I deleted showed up there and I got rid of them. Mistakes are a great learning tool.

�Mike on 12/19/2009: If I were to add a field in a Table, will it automatically update the Form?

Richard Rost on 12/20/2009: Mike, no. You will have to open the form in Design View and add the field from the field list or toolbox.

Dianne �Slade on 2/23/2010: In Access 102 Lesson 7, the CustomersWithFullAddressQ had 4 entries, but the CustomersWithFullAddressR (mailing labels) has only 3. I get all 4 when I run the report.

Reply from Richard Rost:

Was one of them missing some information? Remember, we excluded companies with NULL data in any address field.

�julius on 2/23/2010: Hi, this is julius, i want to ask you about field and data type. Is it possible to make an increment on text data type rather than autonumber data type? For example I want to use a prefix on my id number let's say A123, when i go to the next page the next number should be A124 and can i edit it also coz autonumber data is not editable? Thanks and God speed.

Reply from Richard Rost:

You cannot do this with any of the built-in data types in Access. You would have to create your own text field and increment the counter yourself with VBA code, much like I do in this tutorial.

John�Williams on 2/25/2010: I have been working through the Access 101 and 102 and am making good progess. I have had difficutly in following Lesson 5 (14mins in)in practice. When I right click it to insert an OLE objectI do not find an 'Insert Image' key or when I try to drag an icon of an image into the field box it will not move into the box. I am using Access 2000, please tell me what am I doing wrong? regards John

Reply from Richard Rost:

Add your OLE object field to your table and then your form. Right-click on it in FORM VIEW mode and select INSERT OBJECT. Select CREATE FROM FILE. Now pick your image file. I just tried it with Access 2000 (to refresh my memory since it's been so long) and it works fine in 2000.

Add Your Questions or Comments