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

Selasa, 20 November 2012

Re: [MS_AccessPros] Extracting Month and Year

I meant to say "fixup" - stupid spell check in Outlook.

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: John Viescas <JohnV@msn.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Tuesday, November 20, 2012 7:28 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Extracting Month and Year

Jo-

It's SQL. Create a new query and switch to SQL view. Copy and paste my
code (watch out for line breaks) and mixup the table and field names.
Switch to Design view to see if Access likes it, then run it - on a backup
copy of your table!

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 7:18 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: 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
>

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

Yahoo! Groups Links

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

Yahoo! Groups Links