Mixed dates

  • Thread starter Thread starter Asking a question
  • Start date Start date
A

Asking a question

I'm new to Access, and trying to create a chronology. The problem is that
some of the dates are '01/01/2005' and some are just 2005, or '01/2005'. It
doesn't like these formats. What can I do? Thank you!
 
"2005" isn't, in Access terminology, a "date"... nor is "01/2005".

If you wish to use Access' date/time features/functions, you'll need to
decide how to convert so-called dates to actual dates.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You have several choices.

OPTION ONE (and perhaps the best depending on whether you need to search and
sort by date ranges):
Use a date field to record all dates
If the date is only partial then use additional fields to identify why
So you have
EventDate
EventDateApproximate (leave empty if date is accurate, otherwise use
something like YearOnly, MonthAndYearOnly to indicate partial dates)

For approximate dates with year only enter June 30 plus the year
For approximate dates with year and month enter the 15th day

Option Two:
Use three fields to record the information and leave the month and day fields
blank as appropriate (or enter zero).
TheYear, TheMonth, TheDay

Option Three:
Use a text field and enter all dates in the format YYYY/MM/DD and enter 00 for
missing months and days.

Option Four:
Use a number field (type long) and enter all dates in the format yyyymmdd

Each of those options has drawbacks.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
It seems you are using a text field instead of a DateTime data type.

BACKUP DATABASE BACKUP DATABASE

Open your table in design view and add another field the is data type
DateTime.

Run an update query on the new field with this in the 'Update To' gird space
--
CVDate([Your_Old_Text_Field])
You will need criteria on a calculated field like this --
Len([Your_Old_Text_Field])
and criteria >4
This will convert the text to a date for all records that Access recognizes
as a date.

Run a second update query with criteria on new field of Is Null so as to
not replace data already converted.
Use this --
CVDate("1/1/" & [Your_Old_Text_Field])

You will need to manually review and update the rest.
 
The CDate function will work with 01/2005

CDate("01/2005") = 1/1/2005

However it won't work properly with just 2005.

CDate("2005") =6/27/1905

Also CDate will error out if given something bogus like 13/13/2009 or a null.

You could use the IsDate function to find problem dates and fix them
manually first.

See if something like below will work in a query. Make sure that TheDate is
replaced with the proper field name. Also note that it will return a bogus
date of 1/1/1950 if it can't change the string to a date. You might want to
change this value to meet your needs.

TheDates: IIf(Len([TheDate])<6,CDate("01/" & [TheDate]),
IIf(IsDate([TheDate])=True, CDate([TheDate]),#1/1/1950#))
 
I'm new to Access, and trying to create a chronology. The problem is that
some of the dates are '01/01/2005' and some are just 2005, or '01/2005'. It
doesn't like these formats. What can I do? Thank you!
Click to expand...

An Access Date/Time field is stored as a Number, a count of days and fractions
of a day since midnight, December 30, 1899. As such, it's always and
necessarily a precise instant of time. "2005" was not a precise instant of
time, so it can't be stored as such in a Date/Time field!

You'll need to use another approach. One would be to use a Text field,
preferably in chronological order (2005-01-25 for example, with code on data
entry to either cast it into that form or validate that it's a real date); or
three Integer fields for the year, month and day (don't use Year, Month or Day
as fieldnames though, they're reserved words); or just store the first date of
the possible range - e.g. "2005" becomes 1/1/2005, "August 2005" becomes
8/1/2005. If you do so you might want a second field noting the
approximation.
 
Back
Top