Avatar of Anthony
Flag for United States of America asked on

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"
    fnSecNumFull = "0000"

End If
End Function

Open in new window

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

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?

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Ok thanks Pat.  I'll convert it to a table then.  

I appreciate all the information.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes