Ships from: Burgin, KY
Usually ships in 1-2 business days
Ships from: San Antonio, TX
Usually ships in 1-2 business days
Ships from: Oxford, MA
Usually ships in 1-2 business days
Ships from: fallbrook, CA
Usually ships in 1-2 business days
Trouble constructing a Microsoft Access database? Confounded by error messages?
With this handy "Troubleshooting" guide, it's easy to pinpoint — and solve — your own software problems. Fast! Each section opens with a troubleshooting chart to help quickly diagnose the source of the problem. It offers clear, step-by-step solutions to try right away, plus a full chapter of things to do to stay out of trouble or learn a new trick. Continuous support via the Troubleshooting "Latest Solutions" Web site provides monthly updates on additional problem solving information. Books in the "Troubleshooting" series are colorful, superbly organized, and easy to read, giving even novice users the confidence to fix it themselves — without calling tech support or wasting time on futile trial and error. Covering both Access 97 and Access 2000, this book shows how to troubleshoot access tables, forms, reports, and data access pages; share data; and more.
| About this book | xi | |
| Troubleshooting tips | xiii | |
| Charts and graphs | 2 | |
| I made changes to a chart in Microsoft Graph, but they were not retained | 4 | |
| The legend labels on my chart don't say what I want | 6 | |
| The columns in my chart aren't in the order I want | 8 | |
| My chart appears dimmed or blank | 10 | |
| Controls--Managing data | 12 | |
| The order of the rows in my list box or combo box isn't right | 14 | |
| I see #Error or #Name? in a field instead of the value | 16 | |
| I don't get the right results with conditional formatting | 18 | |
| I need to deal with Null values in a form or report | 20 | |
| The text box control labels are in the way | 22 | |
| The items in my list box or combo box take too long to appear | 23 | |
| Controls--Placing and formatting | 24 | |
| My option group doesn't work the way it should | 26 | |
| I'm having trouble creating, organizing, and sizing a tab control | 28 | |
| I can't make an image fit on a command button | 30 | |
| I get an error message when I click a hyperlink | 32 | |
| Conversion | 34 | |
| I'm having trouble converting an Access 2.0 database | 36 | |
| My code won't compile | 38 | |
| I see a message that an object library is missing | 40 | |
| I see an error message about being out of memory or having too many indexes | 42 | |
| Data--Setting field properties | 44 | |
| Some data is incorrect | 46 | |
| The values in a field don't have the same format | 48 | |
| I don't know what information to enter in a field | 50 | |
| I'm having trouble converting a field to another data type | 52 | |
| Datasheets--Entering and editing data | 54 | |
| I can't enter or edit field data in table | 56 | |
| I get error messages when I try to paste records into a datasheet | 58 | |
| I can't enter dates the way I want in an input mask | 62 | |
| I have only part of the field value and can't find the record I want | 64 | |
| Datasheets--Viewing data | 66 | |
| I can't find the records I want | 68 | |
| I can't change the way my datasheet displays data | 70 | |
| I can't get my subdatasheet to look the way I want | 72 | |
| The size and behavior of my subdatasheet are wrong | 76 | |
| Exporting | 78 | |
| Numbers don't look right after I export them to a text file | 80 | |
| I don't see the subform in my report when I publish it with Word | 82 | |
| Exporting Access data to dBASE or Paradox causes problems | 84 | |
| I get errors when I export a table to Excel | 86 | |
| Excel doesn't show the totals in the report I exported | 88 | |
| Expressions | 90 | |
| The expression I used displays #Error | 92 | |
| The text I combined with a field value isn't displayed | 94 | |
| The mailing labels I created have blank lines | 96 | |
| I'm getting the wrong results with date calculations | 98 | |
| Filtering | 100 | |
| My filter doesn't return all the records I want | 102 | |
| My filter returns too many records | 106 | |
| My filter returns the wrong records | 108 | |
| The filter I created isn't saved with my table | 110 | |
| The list of values in Filter By Form is wrong | 112 | |
| Forms--Designing | 114 | |
| My form is the wrong size and doesn't display complete records | 116 | |
| I can't seem to synchronize two forms | 118 | |
| I can't get my text boxes and labels to line up right | 120 | |
| I'm having trouble aligning a background picture in my form | 124 | |
| I can't get the tab to move in the sequence I want | 126 | |
| Forms--Viewing data | 128 | |
| I'm having trouble entering records in my combo box or list box | 130 | |
| My subform doesn't print the way I want | 132 | |
| My form is blank where there should be data | 134 | |
| My subform doesn't show the data I expected | 136 | |
| Importing and linking | 138 | |
| I can't open or update a linked table | 140 | |
| I'm having trouble with an imported table | 144 | |
| I get errors when I import a spreadsheet or text file to my Access database | 146 | |
| I get errors when I try to append a spreadsheet or text file to an Access table | 148 | |
| Macros | 150 | |
| I can't put a multiline message in a message box | 152 | |
| I get a "Can't find the macro" error message | 154 | |
| My SetValue macro runs at the wrong time | 156 | |
| I can't copy with Ctrl+C anymore | 158 | |
| Menus | 160 | |
| I can't restore my built-in menu bars | 162 | |
| I can't reset my built-in menus | 164 | |
| Some of the built-in menus or commands aren't displayed | 166 | |
| My custom menus don't show up when I open my form | 168 | |
| My shortcut menus aren't right | 170 | |
| Pictures and OLE objects | 172 | |
| I can't open an OLE object | 174 | |
| My calendar control doesn't look right and shows the wrong date | 176 | |
| I see an icon where I put a picture | 178 | |
| I can't edit my OLE object | 180 | |
| The picture is distorted and doesn't fit the frame | 182 | |
| Queries--Action | 184 | |
| My action queries cause errors | 186 | |
| I can't get my append query to work right | 188 | |
| Using a Find Duplicates query to delete duplicate records didn't work | 190 | |
| I'm not getting the results I need with a parameter query | 192 | |
| Queries--Calculations | 194 | |
| I keep getting blanks instead of real values | 196 | |
| I get a strange result when I use the First function in a query | 198 | |
| My query shows the wrong calculation results when I try to summarize data | 200 | |
| My AutoLookup query isn't working | 202 | |
| Queries--Crosstab | 204 | |
| Too many columns in my crosstab query caused an error message | 206 | |
| Saving a crosstab query as a report doesn't work | 208 | |
| I'm having trouble grouping rows in my crosstab query | 210 | |
| I'm having trouble grouping columns in my crosstab query | 212 | |
| Queries--Selection criteria | 214 | |
| I get the wrong records when I combine criteria | 216 | |
| What's up with my wildcard characters? | 218 | |
| I see too many records in the query results | 220 | |
| My query doesn't return as many records as I expected | 222 | |
| Queries--Simple select | 224 | |
| I don't see all the records from one of the tables in my query | 226 | |
| The records aren't in the order I wanted | 228 | |
| The query doesn't display the number of columns I expected | 230 | |
| My query takes too long to return the results | 232 | |
| I can't sort on a lookup field in my query | 234 | |
| Relationships | 236 | |
| I get an error message about ambiguous outer joins | 238 | |
| Access won't let me edit or delete a record | 240 | |
| I can't build the relationship I want in a query | 242 | |
| I can't enforce referential integrity on a relationship | 244 | |
| Reports--Creating | 246 | |
| Calculated fields are empty in report preview | 248 | |
| I can't group records in a report the way I want | 250 | |
| I can't keep duplicate data out of my report | 252 | |
| My report snapshot isn't working right | 254 | |
| Reports--Previewing and printing | 256 | |
| When I print my report, every other page is blank | 258 | |
| My report has too much white space | 260 | |
| Some of my mailing labels are blank | 262 | |
| My multiple-column report isn't printed right | 264 | |
| Sorting | 266 | |
| My records aren't in the order I want | 268 | |
| I'm having trouble sorting on lookup and memo fields | 270 | |
| I can't sort a text field in numeric order | 272 | |
| I can't sort grouped records in a report the way I want | 274 | |
| Table design | 276 | |
| I get an error message after designating a primary key | 278 | |
| I need to control errors in my data | 280 | |
| Too many records have the same value in the index | 282 | |
| I don't know which data types and properties to choose in a new table | 284 | |
| Toolbars | 286 | |
| I can't restore my built-in toolbars | 288 | |
| I can't reset my built-in toolbar buttons | 290 | |
| Some of the built-in buttons aren't displayed | 292 | |
| I can't customize the toolbar I need | 294 | |
| When I try to paste an image on a button, it doesn't look right | 296 | |
| Index | 299 |
I can’t enter or edit field data in a table
When all you’re trying to do is update the tables in your database with new or revised data, it can be a pain to have Access say you can’t do it. There are several fairly simple reasons why you might not be able to edit or enter data in your table.
Another possible reason your value isn’t accepted is that you are entering a number that exceeds the field size setting for a number field. Access might round off the number you enter to the largest value allowed. You might also be trying to enter too many characters in a text field. The default field size for a text field is 50 characters, but the field you’re working with might allow fewer than 50. The following solutions show how to handle each of these problems.
If Access changed a number value you entered or if Access displayed an error message about the value not being valid, follow these steps:
(Image Unavailable)
If your problem lies with the size of a text field, follow these steps:
(Image Unavailable)
When you are entering or editing data in a table too large to view on one screen, keeping track of which record you are working on can be difficult. Keeping one or more of the important columns on the screen while you edit can help orient you.
To do this, use the freeze column feature. Select the column or columns you want to remain visible, and then click Freeze Columns on the Format menu. As you scroll to other columns, the frozen columns remain visible at the left side of the table. When you are through editing the data, you can unfreeze the columns by clicking Unfreeze All Columns on the Format menu. Unfortunately, Access doesn’t put the thawed columns back where they belong, so you will have to drag them to their previous positions.
I get error messages when I try to paste records into a datasheet
Pasting records into a table sounds so easy. Just like fourth grade. Unfortunately, Access is a little pickier than your fourth grade teacher was. If Access can’t paste any or all of the data you want to move or copy, you will see a message describing the problem with a clue about how to fix it, just like the messages on the homework you got back from your teacher. After explaining the problem, Access displays another message, indicating that it has saved all the records that it couldn’t paste in the Paste Errors table. Reviewing the Paste Errors table is a convenient way to troubleshoot the problem you have.
Access might not be able to paste data for several reasons, including the following:
The following solution shows how to deal with these sticky pasting problems.
(Image Unavailable)
(Image Unavailable)
(Image Unavailable)
(Image Unavailable)
A couple of other pasting problems
There are a few other problems you might encounter when pasting records from one table to another in Access. One possibility is that the field you are trying to paste into might be in a hidden column. If so, go to the destination table in datasheet view and click Unhide Columns on the Format menu.
You might also be pasting a column name instead of data into a cell. This happens when you select data in a datasheet by clicking the left margin of the cell. You actually select the column name along with the field value. Then, when you paste the value, you are pasting only the first section of the copied text, which is the column name. Delete the column name from the destination table, and select only the data in the cell, not the complete cell. Then copy and paste it into the receiving record.
Another problem could be that you did not select a destination for the field or fields you want to paste. Or, you might have tried to cut data from or paste data into a field that is locked, unavailable, or calculated, or an AutoNumber field.
The reason for a pasting problem could be as simple as lack of permission to add or edit data in the destination database. If that’s the case, reset your database permissions and try pasting the records again. You can set database permissions by clicking Security on the Tools menu. One other pasting problem might be that the primary key or unique index field of the record you are pasting is a duplicate of one already in the destination record. If that’s the case, you’ll need to edit the primary key value before you can successfully paste the data.
I can’t enter dates the way I want in an input mask
As much as you’d like to think that you can make a process as simple as entering data foolproof, we all know that errors can cunningly sneak into our data through the back door. One of the really helpful features of Access, the input mask, which tries to control what and how much data you can enter in a field, can still generate a few problems of its own.
The input mask displays fill-in spaces, often punctuated with special characters such as slashes (/), commas, and periods. An input mask often limits the number of characters you enter in a field, which can cause problems when you have a longer or shorter value to enter.
Input masks are very particular. If you often hear a beep when entering data in a field, an input mask may not match the way you are used to entering data. For example, if you enter a date as 11502 (meaning January 15, 2002), an input mask that requires six characters could display the value 11/50/2 instead of 01/15/02 because you didn’t enter the first 0. And, neither the month or day can have a value of 50, so this results in an error.
You might also have trouble entering a date in a field with an input mask if the mask conflicts with the display format. For example, dates might be displayed in the format 6-June-2002, while the input mask shows __/__/__. This results in you having to enter a value that doesn’t look like the values already in the field. You will hear a beep if you don’t fill in all the blanks or if you try to enter the name of the month that appears in the displayed value.
Here are some solutions for these problems with input masks.
If you see an error message that a date value isn’t appropriate for the input mask, do the following:
(Image Unavailable)
If the input mask conflicts with the format in which a date field is displayed, take these steps to correct the problem:
(Image Unavailable)
Why is a medium date format better?
To correct the problem with seeing dates in a format that’s different from how you’re asked to enter them, you could also change the field’s Format property to Short Date. However, changing the format to Medium Date is a better solution because it eliminates any ambiguities that can result from the Short Date format. The Medium Date format shows the abbreviated month name instead of all numbers. With the Short Date format, however, if you enter 08/13/02, Access interprets it as 13-Aug-2002, but if you enter 13/08/02, the date displayed is 02-Aug-2013. If you enter a date in which both of the first two entries are less than 12, Access treats the first entry as the month and the second as the day: 10/08/02 is interpreted as 08-Oct-2002, while 08/10/02 is interpreted as 10-Aug-2002.
I have only part of the field value and can’t find the record I want
It’s pretty easy to find everyone listed in your database with the last name of Johnson. When you try to find all the records that mention cats in a memo field, however, it can get a bit tricky. Or maybe you want to locate all the businesses on 5th or 6th Avenue so that you can bombard them with ads. If you know part of a value and that part is at the start of a field, you can simply sort on that field and track down the information you need. But difficulties arise if the part of the value you know is embedded in the field. In these cases, you have to resort to the old poker scam of wildcards, which are symbols that take the place of one or more letters or numbers.
You might have already used these special characters in place of real ones and had a problem with the values that turned up. Wildcards are usually used to search text and memo fields, but, if you are careful, you can massage them to search in date and number fields as well.
If you’re not finding the right records, you might be using the wrong wildcard. If you are using more than one symbol, they might be placed in the wrong order. Another problem might be options set in the Find and Replace dialog box.
If you have tried to find data that includes one of the wildcard characters, you might not have found the records you expected. Looking for a wildcard character takes special preparation because Access thinks you are using it as a wildcard instead of it being the character you want to find. You’ll have no problem looking for values that include exclamation points (!) or closing brackets (]), but other symbols need special treatment.
Here are some ways to use wildcards to your advantage.
If you want to find records with a certain value in a field, for example, any order for any product with "tofu" in its name, do the following:
(Image Unavailable)
If you are looking for addresses on specific streets (in this example, 5th or 6th Avenue), you can mix wildcards with text as follows:
(Image Unavailable)
Overview
Trouble constructing a Microsoft Access database? Confounded by error messages?
With this handy "Troubleshooting" guide, it's easy to pinpoint — and solve — your own software problems. Fast! Each section opens with a troubleshooting chart to help quickly diagnose the source of the problem. It offers clear, step-by-step solutions to try right away, plus a full chapter of things to do to stay out of trouble or learn a new trick. Continuous support via the Troubleshooting "Latest ...