Create a function using expression

[Kassy]

Can anybody help me turn a query expression into a function?
I have been using an expression to calculate dates based on the sytem date CODE: Between IIf(Format(Date(),"mmdd")>"0831",DateSerial(Year(Date()),9,1),DateSerial(Year(Date())-1,9,1)) And Date() Which returns sets of dates from the first of September of a year through to next September of the following year depending on which side of the year you run it to give me a Season of one year that is current according to the system date. I hope this makes sense! I want to be able to set the year to 1st September as the start date of the first part of the current year up to next september 1st when the year start will increment by 1. The join date for members will fall into the Season according to the year regardless of exactly what the date is as long as it is within the 365 days of the year start date. E.g.
If members join 9th September 2005 or January 21st 2006 the Season will be the same for them both i.e. Season 2005-06. Then Sept 1st 2006 becomes Season 2006-07 etc for all members registering within this period. I want to turn it into a function so that I can use it in reports and forms without having to manually change the year or season because it will automatically update according to the system date. Is this feasible or possible -it's beyond my limited ability!

 

[Bat17]

Not quite sure but this may do what you want.
Feed it the date joined and it will return the Season Year, I.E. Join 1 sep 2004 and get 2005, join 31 Aug 2004 and get 2004, join 1 Sep 2005 and get 2006.
We could tweak it though so it returns 2004/2005 etc.
I would use it to create an extra clomun in a query and use that for filtering/grouping.

Function CheckSeason(DateRegistered As Date) As Integer

If Format(DateRegistered, "mmdd") > "0831" Then
    CheckSeason = Year(DateRegistered) + 1
Else
    CheckSeason = Year(DateRegistered)
End If

End Function

HTH

Peter

 

[Kassy]

thanks

will try ASAP Let you know.

 

[Kassy]

Cant get function to work

Ive tried making a new module and savd it as SeasonsFunction. I changed the registerd Date to PaymentDate the same as the field in my table where the date is stored, I then Enterd a text box onto my Form and typed in = (CheckSeason) but it returns #Name. I also tried using the name I saved it by SeasonsFunction, but still no luck. I tried using CheckSeason(), but still no luck. Now Ive got a problem -Error accessing Network connection may have been lost. Most things still work however when I try to run the Form now it wont open and I also get messages about network connection lost. I'm pulling my hair out. I have made copy versions as I'm progressing but I had made quite a few alterations today tidying things up so to go back to my last version means redoing it all again.

 

[Bat17]

You need to pass it a date.
=CheckSeason(#01/01/2006#) or =CheckSeason([paymentDate])

Also make sure that it has not defaulted to a 'Private' function.
Network connection usualy means that the network went down, Access is very sensitive to Netwok problems and will often die when other programmes don't seem to notice. It may have corrupted the form, have you tried compact & Repair?

Peter

 

[Kassy]

Re message Batt17

Sorry been so long getting back but DB has all sorts of probs now. iv'e spent hours redoing things but I just seem to get more probs or the code reverts back again after a period of time. Feel like chucking it in bin. i thought whole idea of Access was for people like myself with no programming knowledge to be able to develop a database. It was easy to begin with to use backups to restore and redo more actions but now Iv'e learnt more and was progressing faster it's much harder to go back to the last saved copy and remember every little tweak I made within a couple of hours. Very annoying and I was beginning to learn quite a bit and enjoy it 'past tense'! Iv'e discovered the use of Macros and now have quite a few to automate opening and closing menus reports etc which Ive gradually changed for event actions for buttons I created however the system falls over and low and behold despite saving and saving again Ive suddenly lost the macro buttons reverting back to Event procedures. Id got to the point where I thought I was progressing enough to learn about functions to do things. Now whole menu options seem to be corrupted and no, compact and repair DB doesnt seem to solve anything
I now get undefined function 'Day' in expression or undefined function 'format' in expression in macros, suddenly reports wont open without a msg box parameter query 'now' waiting for an input which incidentally can take any nonsence! these things worked up until I tried using functions and I havent changed my queries -so I'm stumped and cheesed off! final straw another form is corrupted wont even open so delete it from DB and try to import same form from previousbackup. It doesnt appear. If I tr to change the name and import it as something else it says conflict wih another module blah blah blah.
I looked at the access repir centre recommende in th access forum but was quoted *£80 -not a lot I agree but for a learning curve come favour for a small rugby club I have no intention of wasting my money as well as the weeks of time Ive already used.

 

[Bat17]

some of those errors could be due to a reference problem.
In any Module go Tools>references....
look down the list for any that are ticked but marked (missing). If it is a libary that you are not using then just untick it and restart. If you are using it, it will sometimes clear itself if you clear the tick, shot the DB and then open it up again and reselect it.
With this much problems I would probably create a new DB and try importing everything accross into that.

Peter

 

[Kassy]

NewDB import same problems?

I have already tried making a newDB(once or twice before but problems re-occur sooner or later ) and importing everything into it but havent tried that again because I thought I would just be importing the same code and/or problems. i.e. if something isnt working in my DB why would the problem dissapear by copying to a new database? Why when I close the DB and re-open it I find an Event procedure in place of the macro I had set to the button to(and SAVED/and was Working!) In fact some of the work I do even though I know I have saved it doesnt stay the same and I am having to repeat it.

 

[Bat17]

Importing can leave system corruption behind, I think! I have never had problems like you are experencing though.

Peter

 

[Kassy]

Have sent Db

Have sent DB.