One of the most common data cleanup needs is converting date information. The two-step process and the concepts for doing this are the same for FoxPro and Access. Arrange the numerals in mmddyy order and put in legal separators (hyphens or slashes) between month, day and year. This result may look like a date, but it's still a string of characters that you can't use for calendar math or date arithmetic. Step two is required to convert this correctly arranged date string into a date data type.
The only differences are in the details, which are minor, like the British and the American ways spelling "realisation." Other Tech Tips have explained the FoxPro steps. This one will show the Access how-to.
The word string is used a lot when talking about computer databases. But it's not a technical term; it's a good metaphor that helps you think about what you can do with strings of characters - cut them apart and tie them together. String functions are what you use for the cutting apart. The ampersand symbol is how you tie pieces together.
You only need two string functions: one that picks out a certain number of characters starting from the left, and another that picks out a certain number of characters starting anywhere in the string.
Assume a table with date of birth information, stored in a character field. I'll call that field cDOB, for "character date of birth." The new field to hold the date data-type I'll call DOB.
If the cDOB column has data like 112184 then step one involves arranging it into 11/21/84.
Here are the string functions to do that. Left (cdob, 2) & "/" & Mid (cdob, 3, 2) & "/" & Mid (cdob, 5, 2)
If the date information looked like 841121, you'd rearrange the pieces accordingly to get the same result of mm/dd/yy.
Step two is converting this character representation of a date into a date datatype.
In Access, this means wrapping CVDate() around the entire command above. Since there are already lots of parentheses, I spread out the two parentheses belonging to CVDate() to make things clearer. (In Access SQL, anywhere you can put one space, you can put several, or even a new line.)
CVDate ( Left ( cdob, 2) & "/" & Mid ( cdob, 3, 2) & "/" & Mid ( cdob, 5, 2) )
Putting this fragment together in an update query for our example table (called Employees) looks like this:
UPDATE employees SET dob = CVDate ( Left ( cdob, 2) & "/" & Mid ( cdob, 3, 2) & "/" & Mid ( cdob, 5, 2) )
This works, but know that data isn't perfect. Not all combinations of numbers that may have been typed into a government database to represent a real calendar date actually will. We know, and Access knows, that there is no such date as the 30th of February or the 41st of November. If your update query finds any invalid character dates like this, you'll get this message:
Microsoft Access can't update all the records in the update query.
Microsoft Access didn't update field(s) due to a type conversion failure.
Click the Yes button to go ahead with the update. All the character dates that couldn't be converted to a calendar date will be set to empty or Null. Run a query to show all the rows where the date column you just updated is Null.
SELECT * FROM employees WHERE dob is Null