Strange problem using VB Code in Query

  • Thread starter Thread starter JString
  • Start date Start date
J

JString

I've run into a strange problem that I've never seen before... I wrote a
function in VBA and plugged it into a query, and it works fine for the most
part but on a few records it returns a value which it was not designed to do.


The function is supposed to loop through the items passed to it and return
the smallest value. Should be pretty straightforward.

The function is:

Public Function Smallest(ParamArray items() As Variant) As Variant
On Error GoTo Err_Handler
upper = UBound(items)
varReturn = items(0)
For i = 1 To upper
If varReturn > items(i) Then varReturn = items(i)
Next i
Smallest = varReturn
Exit Function
Err_Handler:
Smallest = -1
End Function

And the query def:

SELECT [Query2].Qty, [Query1].Qty, Smallest([Query2]![Qty],[Query1]![Qty])
AS Expr2
FROM [Query2] INNER JOIN [Query1] ON [Query2].typeID = [Query1].typeID;

Like I said this actually works for most of the records, but for some odd
reason I will get a few records for which the function returned the larger
value.

Can anyone tell me what I'm doing wrong?
 
I was thinking that the problem had something to do with the VB function so I
tried repacing it with the following expression which should do what I want:

( -1 * ([Query1]!Qty <= [Query2]!Qty) * [Query1]!Qty + -1 * ([Query1]!Qty >
[Query2]!Qty) * [Query2]!Qty)

And I get the exact same result where on some of the records, the larger
value is selected. So is my problem just that I don't understand how the
query actually works or what?
 
Here is my code for a similar purpose:

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function
 
JString said:
I've run into a strange problem that I've never seen before... I
wrote a function in VBA and plugged it into a query, and it works
fine for the most part but on a few records it returns a value which
it was not designed to do.


The function is supposed to loop through the items passed to it and
return the smallest value. Should be pretty straightforward.

The function is:

Public Function Smallest(ParamArray items() As Variant) As Variant
On Error GoTo Err_Handler
upper = UBound(items)
varReturn = items(0)
For i = 1 To upper
If varReturn > items(i) Then varReturn = items(i)
Next i
Smallest = varReturn
Exit Function
Err_Handler:
Smallest = -1
End Function

And the query def:

SELECT [Query2].Qty, [Query1].Qty,
Smallest([Query2]![Qty],[Query1]![Qty]) AS Expr2
FROM [Query2] INNER JOIN [Query1] ON [Query2].typeID =
[Query1].typeID;

Like I said this actually works for most of the records, but for some
odd reason I will get a few records for which the function returned
the larger value.

Can anyone tell me what I'm doing wrong?
Click to expand...

Failing to account for Nulls in your data? Remember, any comparison
involving a Null will return Null, not True. Use Nz() to eliminate that
problem:

Smallest(Nz([Query2]![Qty],0),Nz([Query1]![Qty],0))
 
Back
Top