extract just month/year from table field??

[PAkerly]

I want to write an update query that will look at data in DATE and
update NEWDATE based on the following:

I want just the MM/YYYY to be moved over to NEWDATE

my data in DATE is either MM/YYYY or can be and is in some instances:
MM/DD/YYYY

how could I write a query that would take DATE MM/YYYY and MM/DD/YYYY
and update NEWDATE with MM/YYYY ?


thanks

 

[John W. Vinson]

I want to write an update query that will look at data in DATE and
update NEWDATE based on the following:

I want just the MM/YYYY to be moved over to NEWDATE

my data in DATE is either MM/YYYY or can be and is in some instances:
MM/DD/YYYY

how could I write a query that would take DATE MM/YYYY and MM/DD/YYYY
and update NEWDATE with MM/YYYY ?


thanks

A Date/Time field is actually stored as a number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. As such, it
corresponds to an *exact point in time* - not a full month. You can set the
Format of the field to display just the month and year if you wish, but it's
still got a day (and a time, for that matter, which might be midnight).

Are the fields named DATE and NEWDATE Text fields? or date/time? If they're
text, be aware that Access will not recognize them as dates, and will not sort
them chronologically - the text string "09/1992" sorts after the text string
"01/2010".

Assuming that they are Text, an Update query may work. Back up your database
first, this will irreversibly overwrite any data in NEWDATE.

UPDATE mytable SET [Mydate] = Format(CDate([Date]), "mm/yyyy")) WHERE
IsDate([Date]);

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

I want to write an update query that will look at data in DATE and
update NEWDATE based on the following:

I want just the MM/YYYY to be moved over to NEWDATE

my data in DATE is either MM/YYYY or can be and is in some instances:
MM/DD/YYYY

how could I write a query that would take DATE MM/YYYY and MM/DD/YYYY
and update NEWDATE with MM/YYYY ?

What is the datatype of that DATE field (horrible name - "Date" is the name
of a function and therefore is a reserved keyword that should never be used
for the name of a field).? Is it Date/Time or Text?