ACCESS FEED: Re: [MS_AccessPros] Extracting Month and Year

Selasa, 20 November 2012

Re: [MS_AccessPros] Extracting Month and Year

Jo-

You could try something like this:

UPDATE MyTable
SET NewYear = IIf(Len([Sale Date] & "") = 0, Null, Year(CDate([Sale
Date])),
NewMonth = IIf(Len([Sale Date] & "") = 0, Null, Month(CDate([Sale Date])),

..where MyTable is the name of your table, NewYear is the name of your new
Year field, and NewMonth is the name of your new Month field.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: Jo <jenloutz@catholicweb.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Tuesday, November 20, 2012 6:54 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Extracting Month and Year

Thank you John, Here is a sample of my Sale Date column:
SALE DATE
05/26/2006
1/2000
12/2006
9/2000
09/28/2000
9/2000
10/2000
10/2000
1/2001
1/2001
06/19/2001
7/2001
7/2001
11/2003
1/2004
11/2005
4/2005
05/25/2005
8/2005
07/28/2005
9/2005
10/2005
10/25/2005
11/2005
11/14/2005
11/2005
4/2006
01/18/2006
11/2005
01/26/2006
10/2005
10/2005
02/08/2006

08/01/2008
04/04/2008
04/04/2008

05/16/2008
//2011
01/18/2011
1/1/2011
01/27/2011
01/03/2011
1/20/2009
08/24/2011
10/30/2009

04/01/2011
8/16/2011
2/09/2011
09/01/2011
06/04/2012
7/8/2008
5/3/2012
12/30/2011
12/30/2011

12/08/2011

05/25/2012
05/07/2012

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Jo-
>
> You can't do it by setting the Default Value. You must run an Update
> query to try to parse the old text field and stuff something meaningful
>in
> your new Month and Year columns. What is a sample of the values in the
> text field?
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: Jo <jenloutz@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Thursday, November 15, 2012 7:44 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Extracting Month and Year
>
> I have a very old database that was previously created in an older
>version
> of Access but I am now using Access 2010.
>
> My table has a column titled SALE DATE and the data is stored as text.
> Some dates have just month and year others have month, day and year. I
> need to create two new columns - one with just the month and the other
> with just the year.
>
> I am trying to add an expression into the default value field to
> accomplish this but nothing is working. The most common error message I
> get is: The database engine does not recognize either the field 'SALE
> DATE' in a validation expression, or the default value in the table
>'SALES
> DATABASE'.
>
> Please help.
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links