Nov 02
You may need to express a value as an ordinal number. For example, Today is the 21st day of the month. In this case, the number 21 converts to an ordinal number by appending the characters st to the number.
The characters appended to a number depend on the number. There is no clear pattern, making the construction of a formula more difficult. Most numbers will use the th suffix. Exceptions occur for numbers that end with 1, 2, or 3-except if the preceding number is a 1 (numbers that end with 11, 12, or 13). These may seem like fairly complex rules, but you can translate them into an Excel formula.
The formula that follows converts the number in cell A1 (assumed to be an integer) to an ordinal number:
=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(A1))={1,2,3}),
CHOOSE(RIGHT(A1),"st","nd","rd"),"th"))
This is a rather complicated formula, so it may help to examine its components. Basically, the formula works as follows: Read the rest of this entry »
Popularity: 3% [?]
Oct 30
The automatic save feature can pose a problem if you make a change mistakenly. If you’re fast enough, you can use the Undo feature to reverse your last change (Figure 1-12). However, the Undo feature reverses only your most recent edit, so it’s no help if you edit a series of records and then discover the problem. It also doesn’t help if you close your table and then reopen it.
 |
Figure 1-12. The Undo command appears in the Quick Access toolbar at the top left of the Access window (circled), so it’s always available.
|
|
For these reasons, it’s a good idea to make frequent database backups. To make a backup, you simply need to copy your database file to another folder, or make a copy with another name (like Read the rest of this entry »
Popularity: 3% [?]
Oct 28
Counting Specific Characters in a Cell
This formula counts the number of Bs (uppercase only) in the string in cell A1:
=LEN(A1)-LEN(SUBSTITUTE(A1,"B",""))
This formula uses the SUBSTITUTE function to create a new string (in memory) that has all the Bs removed. Then the length of this string is subtracted from the length of the original string. The result reveals the number of Bs in the original string.
The following formula is a bit more versatile. It counts the number of Bs (both upperand lowercase) in the string in cell A1.
=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"b","")) Read the rest of this entry »
Popularity: 3% [?]
Oct 25
Unlike other programs, Access doesn’t require that you save your work. It automatically saves any changes you make.
When you create a new database (Section 1.2.1), Access saves your database file. When you add a table or another object to the database, Access saves the database again. And when you enter new data or edit existing data, Access saves the database almost instantaneously.
GEM IN THE ROUGH
Shrinking a Database |
|
When you add information to a database, Access doesn’t always pack the data as compactly as possible. Instead, Access is more concerned with getting information in and out of the database as quickly as it can.
After you’ve been working with a database for a while, you might notice that its size bloats up like a week-old fish in the sun. If you want to trim your database back to size, you can use a feature called compacting. To do so, just choose Office button  Manage Compact and Repair Database. The amount of space you reclaim varies widely, but it’s not uncommon to have a 10 MB database shrink down to a quarter of its size.
The only problem with the database compacting feature is that you need to remember to use it. If you want to keep your databases as small as possible at all times, you can switch on a setting that tells Access to compact the current database every time you close it. Here’s how:
-
Open the database that you want to automatically compact.
-
Choose Office button Access Options. Access opens the Access Options window where you can make a number of configuration changes.
-
In the list on the left, choose Current Database.
-
In the page on the right, turn on the Compact on Close” checkbox.
-
Click OK to save your changes.
You can set the “Compact on Close” setting on as few or as many databases as you want. Just remember, it’s not switched on when you first create a new database.
|
This automatic save process takes place behind the scenes, and you probably won’t notice anything. But don’t be alarmed when you exit Access and it doesn’t prompt you to save changes, as all changes are saved the moment you make them.
Popularity: 3% [?]
Oct 23
You can use the REPLACE function in conjunction with the SEARCH function to replace part of a text string with another string. In effect, you use the SEARCH function to find the starting location used by the REPLACE function.
For example, assume cell A1 contains the text Annual Profit Figures. The following formula searches for the word Profit and replaces those six characters it with the word Loss:
=REPLACE(A1,SEARCH("Profit",A1),6,"Loss")
This next formula uses the SUBSTITUTE function to accomplish the same effect in a more efficient manner:
=SUBSTITUTE(A1,"Profit","Loss")
Popularity: 4% [?]
Oct 20
Access, like virtually every Windows program, lets you cut and paste bits of information from one spot to another. This trick’s easy using just three shortcut keys: Ctrl+C to copy, Ctrl+X to cut (similar to copy, but the original content’s deleted), and Ctrl+V to paste. When you’re in Edit mode, you can use these keys to copy whatever you’ve selected. If you’re not in Edit mode, the copying or cutting operation grabs all the content in the field.
GEM IN THE ROUGH
Copying an Entire Record in One Step |
|
Usually, you’ll use copy and paste with little bits and pieces of data. However, Access has a little-known ability that lets you copy an entire record. To pull it off, follow these steps:
-
Click the margin to the left of the record you want to copy.
-
This selects the record. (If you want to copy more than one adjacent record, hold down Shift, and then drag your mouse up or down until they’re all selected.)
-
Right-click the selection, and then choose Copy.
This copies the content to the clipboard.
-
Scroll to the bottom of the table until you see the new-row marker (the asterisk).
-
Right-click the margin just to the left of the new-row marker, and then choose Paste.
Prestoan exact duplicate. (Truth be told, one piece of data doesn’t match exactly. Access updates the ID column for your pasted record, giving it a new number. That’s because every record needs to have a unique ID. You’ll learn why in Section 2.4.1.)
|
Popularity: 5% [?]
Oct 17
The Excel FIND and SEARCH functions enable you to locate the starting position of a particular substring within a string:
-
FIND: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for case-sensitive text comparisons. Wildcard comparisons are not supported.
-
SEARCH: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for non–case-sensitive text or when you need to use wildcard characters.
The following formula uses the FIND function and returns 7, the position of the first m in the string. Notice that this formula is case sensitive.
=FIND("m","Big Mamma Thornton",1)
Read the rest of this entry »
Popularity: 4% [?]
Oct 15
Power users know the fastest way to get work done is to use tricky keyboard combination like Ctrl+Alt+Shift+*. Although you can’t always easily remember these combination, a couple of tables can help you out. Table 1-1 lists some useful keys that can help you whiz around the datasheet.
Table 1-1. Keys for Moving Around the Datasheet
|
Key
|
Result
|
|
Tab (or Enter)
|
Moves the cursor one field to the right, or down when you reach the edge of the table. This key also turns off Edit mode if it’s currently switched on.
|
|
Shift+Tab
|
Moves the cursor one field to the left, or up when you reach the edge of the table. This key also turns off Edit mode.
|
|
|
Moves the cursor one field to the right (in Normal mode), or down when you reach the edge of the table. In Edit mode, this key moves the cursor through the text in the current field.
|
|
|
Moves the cursor one field to the left (in Normal mode), or up when you reach the edge of the table. In Edit mode, this key moves the cursor through the text in the current field.
|
|
|
Moves the cursor up one row (unless you’re already at the top of the table). This key also turns off Edit mode.
|
|
|
Moves the cursor down one row (or it moves you to the “new row” position if you’re at the bottom of the table). This key also turns off Edit mode.
|
|
Home
|
Moves the cursor to the first field in the current row. This key brings you to beginning of the current field if you’re in Edit mode.
|
|
End
|
Moves the cursor to the last field in the current row. This key brings you to end of the current field if you’re in Edit mode.
|
|
Page Down
|
Moves the cursor down one screenful (assuming you have a large table of information that doesn’t all fit in the Access window at once). This key also turns off Edit mode.
|
|
Page Up
|
Moves the cursor up one screenful. This key also turns off Edit mode.
|
|
Ctrl+Home
|
Moves the cursor to the first field in the first row. This key doesn’t do anything if you’re in Edit mode.
|
|
Ctrl+End
|
Moves the cursor to the last field in the last row. This key doesn’t do anything if you’re in Edit mode.
|
Read the rest of this entry »
Popularity: 4% [?]
Oct 12
In some situations, you may need to replace a part of a text string with some other text. For example, you may import data that contains asterisks, and you need to convert the asterisks to some other character. You could use Excel’s Find and Replace dialog box to make the replacement. If you prefer a formula-based solution, you can take advantage of either of two functions:
-
SUBSTITUTE: Replaces specific text in a string. Use this function when you know the character(s) to be replaced, but not the position.
-
REPLACE: Replaces text that occurs in a specific location within a string. Use this function when you know the position of the text to be replaced, but not the actual text.
The following formula uses the SUBSTITUTE function to replace 2006 with 2007 in the string 2006 Budget. The formula returns 2007 Budget.
=SUBSTITUTE("2006 Budget","2006","2007")
Read the rest of this entry »
Popularity: 5% [?]
Oct 08
You’ll probably spend a lot of time working with the datasheet. So settle in. To make your life easier, it helps to understand a few details.
As you already know, you can use the arrow keys to move from field to field or row to row. However, you might have a bit of trouble editing a value. When you start typing, Access erases any existing content. To change this behavior, you need to switch into Edit mode by pressing the F2 key; in Edit mode, your typing doesn’t delete the stuff that’s already in that field. Instead, you get to change or add to it. To switch out of Edit mode, you press F2 again. Figure 1-10 shows a closeup look at the difference.
Tip: You can also switch in and out of Edit mode by double-clicking a cell. Read the rest of this entry »
Popularity: 4% [?]
Recent Comments