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

Microsoft Access

Last Comment

Anthony

8/22/2022 - Mon

PatHartman

3/31/2015

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.

Anthony

3/31/2015

ASKER

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?

ASKER CERTIFIED SOLUTION

PatHartman

3/31/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

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

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Anthony