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

Selasa, 20 November 2012

Re: [MS_AccessPros] Extracting Month and Year

Hi John,

Forgive me, I'm not that familiar with Access. Where would I run this?

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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@...>
> 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
>