Dlookup /w Like Variable switched (1 Viewer)

[Vagus14]

Hey everyone,

I am at a loss and was wondering if someone could help real quick. I am trying to make this Dlookup Function work. I have searched the forms and have yet to find an answer. Most of the answers involve the variable on the left opposed to the right side. I appreciate your help.

Test = DLookup("[PN_Identifier]", "tblRate", "'" & StrRate & "' Like '*" & [PN_Identifier] & "*'")

I keep getting run-time error 13.

 

[Pat Hartman]

What is strRate? Logically it should be a column name and doesn't belong inside single quotes. You might need to encase it in square brackets however, if it violates proper object naming convention.

Are you sure that PN_Idenitifier is a string?
Are you sure you need to use LIKE at all. If PN_Identifier is always a complete value then using LIKE just slows down the query.

 

[Vagus14]

Good evening Pat,

Thank you for your help. PN_Identifer has strings that I want to look up like S52??-?????-??? and others. The StrRate will be a part number field in a query.

So the PN_Identifer is a field in the tblRates and the StrRate will be a PN. I want to be able to dlookup for example if strRate has S5212-12345-123, if PN_Ident has S52??-?????-??? in the PN column to get a hit.

The end game is to create a function that searches a table in a primary key column for wildcards and spits out the rates per primary key.

 

[plog]

The 3rd argument of the Dlookup is the criteria to apply. Usually it involves comparing a field of the table to a value. You have not done that. Your criteria compares a value to a value. You never tell the Dlookup to apply criteria to any field of tblRate.

"'" & StrRate & "' Like '*" & [PN_Identifier] & "*'"

Everything outside of quote marks is treated like a variable. Therefore you have used 2 variables in your string---StrRate and [PN_Identifier]. The second of which is a very odd name for a variable--you usually don't use brackets to name variables.

My guess is you wanted to use the field [PN_Identifier] in tblRate to compare to StrRate. In that case you would move [PN_Identifier] to before the Like and also inside the double quotes.

 

[Cronk]

Vagus,

In other words

Test = DLookup("[PN_Identifier]", "tblRate", "[PN_Identifier] Like '*" & StrRate & "*'")

 

[Vagus14]

@Cronk I'll give this a shot when I get back to the office. Thank you everyone for your help thus far.

 

[Vagus14]

Alright! I think I have something going. Here's my code below, as you can see the blank cells will default to the part number after the rates are pulled.

How do I make the remaining list default to a specific value instead of the original part?

Thank you so much for your help am at a loss on this.

Function PartPricingRate(strRate As String) As String

AC_Category_S61 = "S61"
AC_Category_S70 = "S70"

'S61 Parts
While strRate Like DLookup("[PN_Identifier]", "[tblRate]", "[PN_AC_Category] = '" & AC_Category_S61 & "'")
strRate = DLookup("[PN_Rate]", "[tblRate]", "[PN_AC_Category] = '" & AC_Category_S61 & "'")
PartPricingRate = strRate
Wend

'S70 Parts
While strRate Like DLookup("[PN_Identifier]", "[tblRate]", "[PN_AC_Category] = '" & AC_Category_S70 & "'")
strRate = DLookup("[PN_Rate]", "[tblRate]", "[PN_AC_Category] = '" & AC_Category_S70 & "'")
PartPricingRate = strRate
Wend

PartPricingRate = strRate
End function

ALRIGHT I got it... SOmetimes so simple:

While PartPricingRate = ""
strRate = DLookup("[PN_Rate]", "[tblRate]", "[PN_AC_Category] = '" & AC_Category_Cotsi & "'")
PartPricingRate = strRate
Wend