This week's Office challenge tests your knowledge of Excel date values and reveals the answer to last week's challenge on Access controls.
Recently, a client received a spreadsheet — a badly designed spreadsheet — and needed help. One of the minor problems was a column full of date strings. You might remember the days when we preceded dates with an apostrophe, e.g., '08/11/2009, '08/12/2009, and so on. Excel is much smarter now, and the apostrophe is unnecessary. The choice was to convert the date strings to valid date values or to compensate for the date strings in all future formulas and functions. It's easy enough to accommodate the date strings, but the client opted for valid date values. How can you convert a column of date strings to valid date values? (This one was so easy, I did it for free.)
Last week, we ask…
How do you get rid of labels when inserting controls into an Access form? This is one of my favorite tips. By default, Access inserts a corresponding label control with most controls. If you don't want the label, you have to select just the label and press Delete to get rid of it. It isn't a big deal unless most of your controls don't require labels. If that's the case, you can inhibit the label as follows:
All subsequent controls of the same type will not have a corresponding label control. To undo the label fix, simply repeat the process, but choose Yes in step 3. Thanks to Cuhlig, Amasa, and Wazz for playing along and answering correctly.
- Click the appropriate control in the Toolbox but don't insert the control just yet.
- Click the Properties icon.
- Set the Auto Label property to No.
- Insert the control.