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

Rabu, 21 November 2012

Re: [MS_AccessPros] Extracting Month and Year

Hi John,

All I needed to do was create the columns first (and add a couple closing parenthesis). I guess I was thinking this query would create the columns in the table. Once I created the columns the query worked great. Thank you very much!

Jennifer

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Jo-
>
> What is the SQL of the query you created?
>
> You should add a SaleYear and SaleMonth field to your table, then run my
> Update query. Your table must have a Primary Key.
>
> 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: Wednesday, November 21, 2012 1:04 AM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: Re: [MS_AccessPros] Extracting Month and Year
>
> Thank you John,
>
> I get an error stating these fields are not update-able. I have created a
> query and added month and year columns simply by typing Month:month([Sale
> Date]) in the heading. The problem with this solution is I cannot then
> filter the data.
>
> My ultimate purpose is to attach a query to an aerial photo program so I
> can see aerial photos parcels that have sold in the last 3 years.
> Currently I cannot sort my data by year with the whole date in one field.
>
> My next solution was to export my table into excel where it is very simple
> to extract the month and year and then import it back into my database.
> There's two problems with that however, one being my picture record is
> lost in the journey. The other problem is I have to create a new form for
> this new table which will be quite labor intensive given the amount of
> expressions that are stuffed into that.
>
> Any ideas?
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > 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@>
> > 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@>
> > 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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>