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

Rabu, 21 November 2012

Re: [MS_AccessPros] Extracting Month and Year

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@catholicweb.com>
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