SkidPrice(G, 48.000,120.000) yields unexpected results

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

To my FAVORITE guys (and gals) in the whole world... (okay =, I need help)

Below: Example A does not work but Example B does.

What I want is the Skid to take a letter, eith G or J and hunt the table for
the size. Which it does but only if I put the letter (and I'm not chnaging
the code everytime I want to switch between G and J), not if I use Skid
which means you should be able to enter either letter. Can someone tell me
why Example A won't let me choose the letter and returns a value of 0 (if I
use Example B I get the correct value of 30)

Big THANKS!
Gina


Example A:
Public Function SkidPrice(ByVal Skid As String, ByVal Width As Single, ByVal
Length As Single) As Single

If Length > 500 Then Length = 999
Crit = "([spFromLength] <= " & Length & ") "
Crit = Crit & "And ([spFromWidth] <= " & Width & ") "
Crit = Crit & "And ([spToLength] >= " & Length & ") "
Crit = Crit & "And ([spToWidth] >= " & Width & ") "
Crit = Crit & "And ([spItemID] = '" & Skid & "') "
UnitPrice = Nz(DLookup("spPrice", "tblStandardPricing", Crit))
If IsNull(UnitPrice) Then UnitPrice = 0
SkidPrice = UnitPrice
End Function

Examble B
Public Function SkidPrice(ByVal Skid As String, ByVal Width As Single, ByVal
Length As Single) As Single

If Length > 500 Then Length = 999
Crit = "([spFromLength] <= " & Length & ") "
Crit = Crit & "And ([spFromWidth] <= " & Width & ") "
Crit = Crit & "And ([spToLength] >= " & Length & ") "
Crit = Crit & "And ([spToWidth] >= " & Width & ") "
Crit = Crit & "And ([spItemID] = '" & "G" & "') "
UnitPrice = Nz(DLookup("spPrice", "tblStandardPricing", Crit))
If IsNull(UnitPrice) Then UnitPrice = 0
SkidPrice = UnitPrice
End Function
 
Nevermind, I got it...

Forgot when typing in immediate window SkidPrice("G", 48.000, 120.000) NOT
SkidPrice(G, 48.000, 120.000)
 
hi Gina,

Gina said:
What I want is the Skid to take a letter, eith G or J and hunt the table for
the size. Which it does but only if I put the letter (and I'm not chnaging
the code everytime I want to switch between G and J), not if I use Skid
which means you should be able to enter either letter. Can someone tell me
why Example A won't let me choose the letter and returns a value of 0 (if I
use Example B I get the correct value of 30)
Click to expand...
I think it is a typo in your code. Use Option Explicit in your module
header:

Option Explicit
Public Function SkidPrice(ByVal Skid As String, ByVal Width As Single, ByVal
Length As Single) As Single Dim Crit As String
If Length > 500 Then Length = 999
Crit = "([spFromLength] <= " & Length & ") "
Crit = Crit & "And ([spFromWidth] <= " & Width & ") "
Crit = Crit & "And ([spToLength] >= " & Length & ") "
Crit = Crit & "And ([spToWidth] >= " & Width & ") "
Crit = Crit & "And ([spItemID] = '" & Skid & "') "
Click to expand...
SkidPrice = Nz(DLookup("spPrice", "tblStandardPricing", Crit), 0)
End Function
Click to expand...


mfG
--> stefan <--
 
Wasn't a type, see answer above to see where my typo was...


Stefan Hoffmann said:
hi Gina,

Gina said:
What I want is the Skid to take a letter, eith G or J and hunt the table
for the size. Which it does but only if I put the letter (and I'm not
chnaging the code everytime I want to switch between G and J), not if I
use Skid which means you should be able to enter either letter. Can
someone tell me why Example A won't let me choose the letter and returns
a value of 0 (if I use Example B I get the correct value of 30)
Click to expand...
I think it is a typo in your code. Use Option Explicit in your module
header:

Option Explicit
Public Function SkidPrice(ByVal Skid As String, ByVal Width As Single,
ByVal Length As Single) As Single Dim Crit As String
If Length > 500 Then Length = 999
Crit = "([spFromLength] <= " & Length & ") "
Crit = Crit & "And ([spFromWidth] <= " & Width & ") "
Crit = Crit & "And ([spToLength] >= " & Length & ") "
Crit = Crit & "And ([spToWidth] >= " & Width & ") "
Crit = Crit & "And ([spItemID] = '" & Skid & "') "
Click to expand...
SkidPrice = Nz(DLookup("spPrice", "tblStandardPricing", Crit), 0)
End Function
Click to expand...


mfG
--> stefan <--
Click to expand...
 
Back
Top