THE TABLE
The import error occurs because Excel stores dates as the number of days since “January 0, 1900.” As you import the data into your database, Access sometimes treats the data as text. This can happen, for example, when the first row of imported data is blank. The result is numbers instead of formatted dates, for example, 42237 instead of 8/21/2015 or 41795 instead of 6/5/2014.
To create the table that will contain dates and their corresponding Excel number values, first open a new spreadsheet in Excel. Label column A as “Numbers” and column B as “Dates. Add the numbers from 1 to 50,000 in column A and their corresponding dates of 1/1/1900 to 11/22/2036 in column B.
To create the list of numbers, enter the value 1 into cell A2. With that cell selected, go to the Home tab and click on the Fill dropdown in the Editing group. Select Series… to open up the Series dialog box. Make sure the Columns and Linear options are selected and that the Step value is 1. Set the Stop value to 50000 (see Figure 1). If you need dates farther into the future, set the Stop value to a larger value. Click OK. Column A now contains the list of numbers from 1 to 50,000.
Now add the dates to column B. The quickest way is to copy the numbers in column A into column B and then change the format to Date. Another quick method is to put the date 1/1/1900 in cell B2 and then double-click the Fill handle.
Finally, import the spreadsheet into your Access database. Make sure the first field is imported as text and the second field as dates. Name the table “Excel Numbers to Dates.”
UPDATE THE DATES
Now that the table is in Access, create a query to update the dates. To help illustrate this issue, I added a simple table named “CashTransactionHistory” to our test database. As you can see in Figure 2, the Date field contains number values rather than formatted dates. Also note that I added a field called “Transaction Date.” We’ll use this in our query to hold the updated (i.e., formatted) dates.
Create a query using the Excel Numbers to Dates and CashTransactionHistory tables as data sources. Link the tables by dragging the Number field in the Excel Numbers to Dates table to the Date field in the CashTransactionHistory table. This links the number values in both tables. Change the query to an update query. Bring down the Transaction Date field and type [Excel Numbers to Dates].[Date] into the Update To row. Run the query. The Transaction Date field will be populated with the date that corresponds to the sequence number in Excel. Save the query as “Update Date Text to a Date” and close it.
Unless you see a need to keep the improperly imported data, go into Design view for the table and delete that field. For our example, open the CashTransactionHistory table in Design view and select the Date field. Press the Delete key. Confirm the delete. Now it will only have data with accurately formatted dates. Before leaving Design view, rename the Transaction Date field to “Date” so that the field has the proper name for any queries that may have been using the old Date field. Save the table.
READER QUESTION
Recently I received a reader question about how to change the sign of a calculation result from negative (-) to positive in a report control. The answer is very straightforward: multiply the entire calculation by -1. In order to do this, put parentheses around the entire calculation and add a minus sign to the beginning. In the case of a negative result for a calculation, it will be changed to a positive.
This strategy works for any calculation where you want to reverse the sign. You would do the same thing if you wanted to put a negative sign in front of a calculation result that turned out positive. Here’s an example using a complex expression:
=-(Sum([TotalAmountPayable]+[VAT]+[LocShip])-([PriceSp])-([AllocatedBudget]))
Note the –( at the beginning and the ) at the end. That’s all that’s needed to change the sign. This will work in a report control, a query, or anywhere else you use a calculation. Thanks for reading and asking questions.
Download this month's database here: SF_SEP_2015
SF BEST PRACTICES
Saving the update query even though it’s a one-time update is a way to document the solution to a problem that you may encounter again. You can also print out the query or create a documentation folder to keep a record of these handy solutions.
September 2015
