Issue With Access VBA Function

Hi Everyone,

I'm having an issue with the below function that I wrote... The query takes over 5 minutes to run for about 8,000 records and I feel like that's super long.  When I go to Stop the query and Debug the code, it's always at the first line "Public Function" that I have the arrow and yellow highlighted line.  Can anyone see why there would be such an issue of running this?

Public Function fnSecNumFull(MBSTOR As Long, SSCHAN As Long) As String

If (MBSTOR >= 100000 And MBSTOR <= 100999) Then
    fnSecNumFull = "0030"

ElseIf (MBSTOR >= 11000 And MBSTOR <= 110199) Then
    fnSecNumFull = "0040"

ElseIf (MBSTOR >= 110300 And MBSTOR <= 111999) Then
    fnSecNumFull = "0040"

ElseIf (MBSTOR = 110262) Then
    fnSecNumFull = "0040"

ElseIf (SSCHAN = 5016) Then
    fnSecNumFull = "0020"

ElseIf (SSCHAN = 5025) Then
    fnSecNumFull = "0021"
    
ElseIf (SSCHAN = 3046) Then
    fnSecNumFull = "0022"

ElseIf (SSCHAN = 5024) Then
    fnSecNumFull = "0023"

ElseIf (SSCHAN = 5047) Then
    fnSecNumFull = "0024"
    
ElseIf (SSCHAN = 5071) Then
    fnSecNumFull = "0025"

ElseIf (SSCHAN = 3042) Then
    fnSecNumFull = "0026"

ElseIf (MBSTOR >= 160000 And MBSTOR <= 160999) Then
    fnSecNumFull = "0050"

ElseIf (MBSTOR >= 110200 And MBSTOR <= 110261) Then
    fnSecNumFull = "0042"

ElseIf (MBSTOR >= 110263 And MBSTOR <= 110299) Then
    fnSecNumFull = "0042"

ElseIf (MBSTOR = 1 Or MBSTOR = 2 Or MBSTOR = 1262) Then
    fnSecNumFull = "0060"

ElseIf (MBSTOR >= 165000 And MBSTOR <= 165999) Then
    fnSecNumFull = "0052"

ElseIf (MBSTOR >= 190000 And MBSTOR <= 190999) Then
    fnSecNumFull = "0054"
    
Else
    fnSecNumFull = "0000"

End If
 
End Function

Open in new window

LVL 1
Anthony6890Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
When you are running a long series of If's, make sure that they are ordered in sequence by frequency.  So, the most frequent value is tested for first, etc.  In your case, you are testing two different variables so that complicates the issue and you may not be able to optimize the sequence.

Personally I dislike the ElseIf construct.  I would use "Select Case True" but I don't know if that would affect the timing.  It could because for every level deep you go with the ElseIF, code to return to the next level needs to run.  the Select Case isn't nested so when a condition is satisfied, the exit is immediate.
Anthony6890Author Commented:
I can't get around the two variables unless I make a stagnant table, which is an option, it just always has to be updated when there is an addition to that table.  

For the Select Case True,

Does it automatically drop out when it comes across a match for the criteria?  I've been reading that, is it true?
PatHartmanCommented:
Yes.  That is why it is important to have the likeliest true value as close to the top as possible.  If you have 20 If statements, it is far better to execute 1 of them than 20 so always order your If's or Cases in descending order of occurrence.

PS - Tables are always better than code for this type of process.  The query engine will take less time to join to the lookup table than it will take for your code to run for all the records in the recordset.  I would have suggested that but the two variables complicates the issue.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony6890Author Commented:
Ok thanks Pat.  I'll convert it to a table then.  

I appreciate all the information.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.