Changing date fotmats & calculating date - HELP

[Robs]

I have 2 queries that I'm desparately in need of help with...

1) I have a date field, where the date has been captured in either
yyyy/mm/dd format, or dd/mm/yyyy format. I need to change all of the dates
that are in dd/mm/yyyy format to the yyyy/mm/dd format. HOW on earth is this
possible??
2) I have 2 dates, both in yyyy/mm/dd format and I need to minus 1 from the
other in order to get a month value e.g 2008/05/01 - 2008/01/01 = 4 months
(May - January = 4) etc....again, how on earth do I do this?

I'm trying to learn Access, and am starting to use ot quite frequently at
work....but as the only Access person here, I don't have anyone to turn to
for help.....so hopefully some kind soul on here can help...

Thanks

 

[John Spencer]

First is the field that holds the dates a datetime field or is it a text field
that hold information that looks like dates?

If it is a text field - are the dates stored with slashes as you indicated?
You should convert the date information field to a dateTime field. The safest
way to do so would be to add a new field to your table to accept the dates.

DateTime fields store the date as a number that consists of the number of days
and partial days from a base date of December 30, 1899. The format they are
displayed in is just that - a display format.

You can use update queries to populate the newdate field. The easy one is
dates that have a text string in the format of yyyy/mm/dd

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE YourTableName
SET NewDateField = CDate(OldTextDate)
WHERE OldTextDate Like "####/##/##"

More complex are the dates in dd/mm/yyyy format.
UPDATE YourTableName
SET NewDateField =
DateSerial(Right(OldTextDate,4),Mid(OldTextDate,4,2),Left(OldTextDate,2))
WHERE OldTextDate Like "##/##/####"

If you don't know how to construct the above queries, post back.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[John Spencer]

OH! For the second part of the question. Use the DateDiff function to
calculate the number of months between two dates.

DateDiff("m",StartDate,EndDate)

The DateDiff function counts the number of times the interval changes.
Dec 31, 2007 to Jan 1, 2008 is counted as a one month change
and
Dec 1, 2007 to Jan 31, 2008 is counted as a one month change

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Robs]

Oddly enough - it's in number format.

I will try the steps below, and see what happens. Will keep you updated, and
also will ask if I need help...

You are my hero, thank you for helping!
Robs

 

[John Spencer]

In NUMBER format!!!

That is going to be fun to handle.
Since Jan 1, 2008 could be stored as
20080101 or 1012008

Depending on date range allowed you could have problems discerning which dates
were stored in which format.
For instance is 20072008
July 20 2008
or
August 08 2007

Good Luck

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Robs]

Yip, Number format - I feel that my life is about to become a lot more
challenging.
I need to contact this client and get them to clean it on their end, before
I do any kind of query running.
I have saved your queries for future use, and ran a test query on one of my
other tables, and it worked.....so you have helped massively.

Again, you're my Hero, I'll keep you updated on the progress of this mess!