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

Selasa, 20 November 2012

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
>