Procedure too large 1

[wbwillson]

I've written a very large procedures which exceeds the 64Kb limit for procedures, so I get an error when compiling saying that the procedure is too large and that I need to break it up. My problem is that this procedures looks at a field on my form which stores U.S. Navy rating (which are essential the person's job title). For example it looks at a field entitle [RATE] and the uses a series of If Then Else states to figure out what the rate is.

If Left(RATE, 2) = "YN" And Right(RATE, 1) = "1" Then
RateName = "Yeoman First Class"
ElseIf Left(RATE, 2) = "YN" And Right(RATE, 1) = "C" Then
RateName = "Chief Yeoman"

etc...

This goes on for all 75+ ratings in the Navy, so the procedure is very large. I've tried breaking it up into two functions but how can I call both of these functions at the same time. I.E., call function 1 if the rate is contained in function1 then move to function2 and then pass the result to a new field call [COMPLETERATE]?

Any ideas would be greatly appreciated.

Bill

 

[TorF]

Why don't you use a table to store datas ?

A table NavyClass looks like :

LeftRate RightRate Name
YN 1 Yeoman First Class
YN 2 Chief Yeoman
...

Then get the name by getting directly it from table:
Name = Nz(Dfirst("Name","NavyClass","LeftRate='" + left(RATE,2) + "' AND RightRate='"+ rigth(RATE,1) + "'&quot, "Unknow&quot

This will be easier than a boring enormous procedure

 

[wbwillson]

Torf,

That is an amazing piece of code! I would have never thought of doing it like that. It works great! Just what I needed. I really appreciate the help!

Happy Holidays

Bill

 

[MichaelRed]

the "+" is the symbol for ADDITION (a MATH fumction), and may cause some problems when being used for concatenation (a string function). I suggest that you replace it with the concatenation operator "&" where appropiate.

A further 'enhavcement; would be to replace the domain aggregate function with a simple select query. Domain aggregates are slower than the equivalent select query.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places