Good morning everyone,
I was wondering if someone could give me a few tips on speeding up a function I made in VBA for a query. The function pulls from a table by Dlookup and determines if a a item is like the wildcard in the table. It pulls 5 times because the wildcard is different in various cases. Here's a sample below, is there anyway to make this more efficient?
Table that the code pulls from is attached.
I was wondering if someone could give me a few tips on speeding up a function I made in VBA for a query. The function pulls from a table by Dlookup and determines if a a item is like the wildcard in the table. It pulls 5 times because the wildcard is different in various cases. Here's a sample below, is there anyway to make this more efficient?
Code:
Function PartPricingRate(strRate As String) As String
'35 Variables
AC_Category_S35_1 = "S35-1"
AC_Category_S35_2 = "S35-2"
AC_Category_S35_3 = "S35-3"
AC_Category_S35_4 = "S35-4"
AC_Category_S35_5 = "S35-5"
'50 Variables
AC_Category_S50_1 = "S50-1"
AC_Category_S50_2 = "S50-2"
AC_Category_S50_3 = "S50-3"
AC_Category_S50_4 = "S50-4"
AC_Category_S50_5 = "S50-5"
'S35 Parts
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_1 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_1 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_2 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_2 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_3 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_3 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_4 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_4 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_5 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_5 & "'")
PartPricingRate = strRate
Wend
'S50 Parts
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_1 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_1 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_2 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_2 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_3 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_3 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_4 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_4 & "'")
PartPricingRate = strRate
Wend
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_5 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_5 & "'")
PartPricingRate = strRate
Wend
PartPricingRate = strRate
End Function
Table that the code pulls from is attached.