Add upcoming renewal dates to your Access database
Thursday, October 30, 2008 12:44 PM
Calculating future dates in Access is easy, thanks to the DateAdd function and SQL UPDATE command. Here's an example.
Microsoft Access
Add upcoming renewal dates to your Access database
When you need to calculate a future date based on a date already entered in a date field, you can use the DateAdd function. Combine DateAdd with the SQL UPDATE command, and you can add a future date to hundreds of records with a single line of code.
For example, say you keep track of the dates when customers subscribe to your five-year warranty service in the table shown in Figure A.
Figure A
The blank column RenewalDate has been added as a Date/Time field. You would like to add a value to the RenewalDate field of each record that indicates when each customer's warranty comes up for renewal.
Follow these steps:
- Open the Query Design Grid.
- Add the Subscriptions and Renewals table.
- Go to View | SQL View, which opens the SQL Window.
- Enter the following code at the prompt, as shown in Figure B:
UPDATE [Subscriptions and Renewals] SET [Renewal Date]
= DATEADD("yyyy",5,[Subscription Date]);
Figure B
- Run the query.
After the query is run, the RenewalDate field will be filled with dates five years from the date entered in the Subscription Date field for each record (Figure C).
Figure C
Microsoft Word
What to do when you can't change the tabs from Word's ruler
Using the ruler to add tab settings is a common practice. However, users often find that when they try to use the ruler to change the tab settings of multiple paragraphs all at once, nothing happens.
For example, say you're formatting some text that has been copied from several documents, each with its own first-line paragraph indent settings (Figure A). When you select the text, all tab markers are grayed out and can't be changed.
Figure A
Fortunately, you don't have to go back and change each paragraph individually. Follow these steps:
- Select the paragraphs whose tabs you want to reformat.
- Press Alt + O + T (or double-click one of the grayed-out tab markers on the ruler..
- In the Tabs dialog box (Figure B), click Clear All and then OK.
Figure B
- Click the horizontal ruler at the 1-inch marker to define the new tab setting for the selected paragraphs.
As Figure C shows, all paragraphs are now indented identically.
Figure C
Microsoft Excel
How to combine text and dates in the same cell
Excel does not normally let you combine text and dates together in the same cell. For example, suppose you have entered the formulas shown in Figure A.
Figure A
Figure B shows the results.
Figure B
While the value in A2 displayed correctly, the value in A1 did not. This is because Excel displays the actual date data entered into the cell until it's formatted. Thus, to show the data in A1 as a true date, we need add the TEXT function to change the format to text. When we replace the formula in B1 with
="Today is"&" "&TEXT(A1,"MM/DD/YYYY")
we get the results shown in Figure C.
There are currently no comments for this post.