ACCESS FEED: RE: [MS_AccessPros] Re: Editing a function

Robin-

The picture you posted looks like it's in a Form module - the wrong place!
There's also a strange character in you screen dump: |

You need to delete all that code (select it and copy to the clipboard) and
do this:

1) From the main window of Access, click on the Create tab, then click on
the down arrow next to Macro. Select Module. That should put you in a
brand new "standard" module.

2) Put the code in this new standard module. Replace the | character with _
and press Enter to put the following code on a new line.

3) Choose Compile from the Debug menu.

4) If the compile returns no errors, click Save and give the new module a
name like modUtilities.

Now run your query - it should return the "next" anniversary date for each
record.

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: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, January 31, 2013 7:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Editing a function

John and Patrick,

The time difference is our enemy. It is now 05:15 Friday morning.

First I need your help to understand 'Standard Module' and whatever the
alternative may be.

I have 'General' showing in a drop down box above the code. Is that a
standard module?

BTW I am an 83 year old fella! I am keen but inexperienced in code.

Many thanks for your interest and help.

I have tested all the options of the code you have prepared in the
'General' area and all give me 2014.

I have added a screen capture on my web site.

http://www.rotary9790.org.au/test/test.asp

That example of code is working but gives 2014 for every entry.

Many thanks again for your patience.

Regards,

Robin Chapple

At 31/01/2013 07:01 PM, you wrote:
>Patrick & Robin-
>
>I found that "bug" a couple of days ago and gave Robin the correction
>for the missing close paren. She originally found that when she pasted
>the code in a module somewhere (probably a form module) and it wouldn't
>compile. I should have re-posted the entire bit of code with
>instructions as to how to put it in a Standard module.
>
>Robin- If you created and saved the code in a standard module, you
>should now see that module in the Nav Pane. Just right-click and
>choose Open to edit it.
>
>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: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>patrickawood
>Sent: Thursday, January 31, 2013 7:12 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: [MS_AccessPros] Re: Editing a function
>
>
>Hi Robin,
>
>I think I found the problem. Try this code exactly as it is. You will
>need to use this to completely replace what you have.
>
>
>Public Function NextAnniversary(datChartered As Date) As Date
>
>Dim intMonth As Integer, intDay As Integer, intYear As Integer
>
> intMonth = Month(datChartered)
> intDay = Day(datChartered)
> intYear = Year(Date)
>
> If (intMonth > Month(Date)) Or _
> ((intMonth = Month(Date)) And (intDay < Day(Date))) Then
> intYear = intYear + 1
> End If
>
> NextAnniversary = DateSerial(intYear, intMonth, intDay)
>
>End Function
>
>
>Kind Regards,
>Pat Wood
>Gaining Access Technologies Founder
>http://gainingaccess.net
>Microsoft Access and SQL Azure Information Center for Developers:
>http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx
>
>--- In MS_Access_Professionals@yahoogroups.com, Robin Chapple wrote:
> >
> > Thanks again Pat,
> >
> > Public Function NextAnniversary(datChartered As Date) As Date
> >
> > Dim intMonth As Integer, intDay As Integer, intYear As Integer
> >
> > intMonth = Month(datChartered)
> >
> > intDay = Day(datChartered)
> >
> > intYear = Year(Date)
> >
> > If (intMonth Month(Date)) Or _
> >
> > ((intMonth = Month(Date)) And (intDay Day(Date)) Then
> >
> > intYear = intYear + 1
> >
> > End If
> >
> > NextAnniversary = DateSerial(intYear, intMonth, intDay)
> >
> > End Function
> >
> > Call that from a query on your table like this:
> >
> > Anny: NextAnniversary([DateChartered])
> >
> > Regards,
> >
> > Robin
> >
> > At 31/01/2013 04:34 PM, you wrote:
> > >Hi Robin,
> > >
> > >Can you post the original Function?
> > >
> > >Pat
> > >
> > >--- In MS_Access_Professionals@yahoogroups.com, Robin Chapple wrote:
> > > >
> > > > Well done Pat,
> > > >
> > > > That found the function but I cannot edit it to give 2013
> > > > instead of
>2014.
> > > >
> > > > From February onwards the next anniversary will be in 2013 but
> > > > all are returning 2014.
> > > >
> > > > I have edited intYear = intYear + 1
> > > >
> > > > to
> > > >
> > > > intYear = intYear
> > > >
> > > > and
> > > >
> > > > intYear = intYear -1
> > > >
> > > > Without the change showing.
> > > >
> > > > I have even edited
> > > >
> > > > NextAnniversary = DateSerial((intYear - 1), intMonth, intDay)
> > > >
> > > > to
> > > >
> > > > NextAnniversary = DateSerial((intYear - 2), intMonth, intDay)
> > > >
> > > > without a change.
> > > >
> > > > 2014 seems to be set in concrete!
> > > >
> > > > Regards,
> > > >
> > > > Robin
> > > >
> > > > At 31/01/2013 04:05 PM, you wrote:
> > > > >Hi Robin,
> > > > >
> > > > >Don't worry about seeing blank until you do the search for the
> > > > >name of the Function according to my instructions I gave in an
> > > > >earlier post. If you have the name right and followed my
> > > > >instructions you will find the Function.
> > > > >
> > > > >If that does not work then perhaps you have the name of the
> > > > >Function wrong. You can then search for the text below which
> > > > >should be in
> > > your Function:
> > > > >
> > > > >intYear = intYear + 1
> > > > >
> > > > >Kind Regards,
> > > > >Pat Wood
> > > > >Gaining Access Technologies Founder http://gainingaccess.net
> > > > >Microsoft Access and SQL Azure Information Center for Developers:
> > > > >http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx
> > > > >
> > > > >
> > > > >--- In MS_Access_Professionals@yahoogroups.com, Robin Chapple
wrote:
> > > > > >
> > > > > >
> > > > > > Thanks for your persistence.
> > > > > >
> > > > > > The function is working but it gives the year as 2014 and I
> > > > > > need
> > > > > it to be 2013.
> > > > > >
> > > > > > So the answers to your queries must be 'Yes'.
> > > > > >
> > > > > > With the query on the worksapce Alt F11 gives me blank pages.
> > > > > >
> > > > > > Cheers,
> > > > > >
> > > > > > Robin
> > > > > >
> > > > > > At 31/01/2013 03:40 PM, you wrote:
> > > > > > >Hi Robin,
> > > > > > >
> > > > > > >Did you try searching as I laid out?
> > > > > > >
> > > > > > >If so, are you sure you saved the Function?
> > > > > > >
> > > > > > >And are you using the database in which you created the
Function?
> > > > > > >
> > > > > > >
> > > > > > >Kind Regards,
> > > > > > >Pat Wood
> > > > > > >Gaining Access Technologies Founder
> > > > > > >http://gainingaccess.net Microsoft Access and SQL Azure
Information Center for Developers:
> > > > > > >http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.asp
> > > > > > >x
> > > > > > >
> > > > > > >
> > > > > > >--- In MS_Access_Professionals@yahoogroups.com, Robin
> > > > > > >Chapple
>wrote:
> > > > > > > >
> > > > > > > >
> > > > > > > > Thanks Pat,
> > > > > > > >
> > > > > > > > I had tried that and once again all I get is blank pages!
> > > > > > > >
> > > > > > > > Cheers,
> > > > > > > >
> > > > > > > > Robin
> > > > > > > >
> > > > > > > > At 31/01/2013 02:49 PM, you wrote:
> > > > > > > > >Hi Robin,
> > > > > > > > >
> > > > > > > > >That's OK. Try pressing the Alt and F11 keys at the
> > > > > > > > >same
>time.
> > > > > > > > >
> > > > > > > > >Kind Regards,
> > > > > > > > >Pat Wood
> > > > > > > > >Gaining Access Technologies Founder
> > > > > > > > >http://gainingaccess.net Microsoft Access and SQL Azure
> > > > > > > > >Information Center for Developers:
> > > > > > > > >http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo
> > > > > > > > >.a
> > > > > > > > >spx
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >--- In MS_Access_Professionals@yahoogroups.com, Robin
> > > Chapple wrote:
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Thanks Pat,
> > > > > > > > > >
> > > > > > > > > > I cannot even find the "" window where you work
> > > > > > > > > > with
> > > the code "".
> > > > > > > > > >
> > > > > > > > > > Woe is me! Sackcloth and ashes!!!
> > > > > > > > > >
> > > > > > > > > > Cheers,
> > > > > > > > > >
> > > > > > > > > > Robin
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > At 31/01/2013 12:37 PM, you wrote:
> > > > > > > > > > >Hi Robin,
> > > > > > > > > > >
> > > > > > > > > > >You can do a search for NextAnniversary was it? In
> > > > > > > > > > >the
> > > > > window where
> > > > > > > > > > >you work with the code on the Menu click "Edit" and
> > > > > > > > > > >then
> > > > > "Find" and
> > > > > > > > > > >enter NextAnniversary in the "Find What:" box at
> > > > > > > > > > >the
> > > top of the
> > > > > > > > > > >dialog that opens and then make sure that "Current
> > > > > > > > > > >Project" is selected and then click "Find Next".
> > > > > > > > > > >Keep clicking "Find
> > > > > Next" until
> > > > > > > > > > >you see the Function.
> > > > > > > > > > >
> > > > > > > > > > >Kind Regards,
> > > > > > > > > > >Pat Wood
> > > > > > > > > > >Gaining Access Technologies Founder
> > > > > > > > > > >http://gainingaccess.net Microsoft Access and SQL
> > > > > > > > > > >Azure Information Center
> > > for Developers:
> > > > > > > > > > >http://gainingaccess.net/SQLAzure/AccessAndSQLAzure
> > > > > > > > > > >In
> > > > > > > > > > >fo.aspx
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >--- In MS_Access_Professionals@yahoogroups.com,
> > > > > > > > > > >Robin
> > > > > Chapple wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > My apology for being a 'lame duck'.
> > > > > > > > > > > >
> > > > > > > > > > > > John Viescas has taken me step by step through
> > > > > > > > > > > > adding
> > > > > a function to
> > > > > > > > > > > > my database. It works as planned with one
> > > > > > > > > > > > exception
> > > > > and I need to
> > > > > > > > > > > > edit one criteria.
> > > > > > > > > > > >
> > > > > > > > > > > > How do I get back to the function to edit it? I
> > > > > > > > > > > > have
> > > > > investigated
> > > > > > > > > > > > everything that looks possible without success.
> > > > > > > > > > > > The
> > > > > Microsoft help
> > > > > > > > > > > > page was no help.
> > > > > > > > > > > >
> > > > > > > > > > > > Your advice is welcome.
> > > > > > > > > > > >
> > > > > > > > > > > > Robin Chapple
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >------------------------------------
> > > > > > > > > > >
> > > > > > > > > > >Yahoo! Groups Links
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >------------------------------------
> > > > > > > > >
> > > > > > > > >Yahoo! Groups Links
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >------------------------------------
> > > > > > >
> > > > > > >Yahoo! Groups Links
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >------------------------------------
> > > > >
> > > > >Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

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

Yahoo! Groups Links