Need Custom Function

[abzalali]

Dear All,

I need a custom function for school database ranking based on year of established year.
1. How many year established till to current year. But if established year before 1981 then year calculate from 1981 to current year.
2. To set for last 10 year 20 point (2 point for each year)
and rest of the each year 1 point

Please help me.

Thanks in Advance
Mir Abzal Ali

 

[CJ_London]

I would investigate using the datediff and dateadd functions

here are some links
http://office.microsoft.com/en-gb/a...ween-two-date-or-time-values-HP001098486.aspx

http://office.microsoft.com/en-gb/access-help/dateadd-function-HA001228810.aspx

I don't understand your second question so cannot help with this

 

[Mihail]

Public/Private Function NoOfYears(EstablishedYear as Long) As Long
   If EstablishedYear < 1981 then
     EstablishedYear = 1981
   End If
   NoOfYears = Year(Date()) - NoOfYears
End Function

Public/Private Function NoOfPoints(YearsNo As Long) As Long
   If YearsNo <= 10 Then
    NoOfPoints = YearsNo * 2
Exit Function
   End If

   NoOfPoints = 20 + (YearsNo-10)*1 'I know :)
End Function

 

[abzalali]

Dear Mihail,

Please help me a bit more. "EstablishedYear" is in my table(tblBasicInfo) and in Column (Established), how can I get those value in function.

Thanks & really gretfull to you
Mir Abzal Ali

 

[Mihail]

Is a bit hard to explain. Hope you know how to create queries.

Copy the functions in a regular module (not in a form module).
Use Public property for each function
Public Function ......

Create a query based on your table
After you will select all the "columns" (in Access this columns are named FIELDS), in the first empty field write:

SchoolAge:NoOfYears(Established)

In the next empty field write

 SchoolPoints:NoOfPoints(SchoolAge)

Run the query AFTER you ask your God for help . That because my code is not tested.

 

[abzalali]

Dear Mihail,
I understand, Now I can do it.

Really Thanks for best cooperation.

Mir Abzal Ali

 

[CazB]

In a query, you could try

Yrs: IIf([Establishedyear]<1981,Year(Date())-1981,Year(Date())-[Establishedyear])

Points: IIf((Year(Date())-[Establishedyear])<10,(Year(Date())-[Establishedyear])*2,20+((Year(Date())-[Establishedyear]-10)))

 

[pr2-eugin]

Just wondering why is everyone using Now function Date should be sufficient..

I know it does not impact very greatly but it makes sense to use Date over Now.. Just my 2 pennies worth..

 

[Mihail]

In a query, you could try

Yrs: IIf([Establishedyear]<1981,Year(Now())-1981,Year(Now())-[Establishedyear])

Points: IIf((Year(Now())-[Establishedyear])<10,(Year(Now())-[Establishedyear])*2,20+((Year(Now())-[Establishedyear]-10)))

This approach is faster than my one, but, in my opinion, harder to maintain.

 

[CazB]

never even thought about it, Paul... just carried on using Now as that's what had been used earlier in the thread!
Wrists duly slapped and earlier post being edited

 

[CazB]

Admittedly, it's harder to maintain and easier to get the syntax wrong, but for someone not familiar / comfortable with writing code, potentially quicker.... just wanted to offer it up as an alternative

 

[Mihail]

@CazB
I understand your point.

@abzalali
I also edited my post after Paul's remark.

 

[abzalali]

Points: IIf((Year(Now())-[Establishedyear])<10,(Year(Now())-[Establishedyear])*2,20+((Year(Now())-[Establishedyear]-10)))

With this syntax Points counts all of years before 1981 but I need to count till 1981

Thanks all of you
Mir Abzal Ali

 

[abzalali]

Dear CazB,
Within this syntax, Is there any way to count points till 1981, if establishedyear <1981

Thanks
Mir Abzal Ali

 

[Mihail]

Replace
<10
with
<=10