Building the results of a function into an SQL statement

[GJP55]

I have written a function that runs through a recordset and find the lowest value from ceratin fields in a row.

I want to use this as a column in a new query but do not know how to incorperate it into the sql query definition.

My function is :

Function LowestPrice()

Dim db As Database
Dim qry As QueryDef
Dim rs As DAO.Recordset
Dim i As Integer
Dim MyArray(100) As Variant
Dim currentVal As Variant

Set db = CurrentDb()
Set qry = db.QueryDefs("MyQuery&quot
Set rs = qry.OpenRecordset
Do While Not rs.EOF
currentVal = 0

For i = 0 To rs.Fields.Count - 1
If rs.Fields(i).name Like "*Price1" Then
MyArray(i) = rs.Fields(i).Value
End If

If MyArray(i) < currentVal Then
currentVal = MyArray(i)
End If
Next
LowestMultiPrice = currentVal
rs.MoveNext
Loop

End Function

I would like to then use results of the function to be a column in an SQL statement.

 

[KenReay]

Hi

Dim strSQL as String

strSQL = &quot;SELECT LowestPrice() As LowPrice,... FROM MyTableName....&quot;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GJP55]

Ken,

Thanks very much, works a treat.

Cheers

GJP