Dear All,

I have a question with regards to SQL and its use in conjunction with functions. Under this link you can see a question which I asked in this forum before:
http://www.access-programmers.co.uk/forums/showthread.php?t=161510

I asked whether there is a SQL function which allows me to retrieve the maximum of various columns of a table (someting like max(Column1, Column2...). However, the result was that there is nothing like that and that I have to use the following function:


Public Function MaxColumn(ParamArray lngArr() As Variant) As Long
Dim i As Integer
Dim max As Long

max = lngArr(0)
If UBound(lngArr()) > 0 Then
For i = 1 To UBound(lngArr())
If lngArr(i) > max Then max = lngArr(i)
Next
End If
MaxColumn = max
End Function


I use the following code in Excel VBA to put together the SQL string with which I can finally retrieve the data out of a txt-file:


Function SQL_ATR() As String
Dim query1 As String, query2 As String, query3 As String, query4 As String
Dim sSQL As String
Dim iPeriod As Integer
Dim dRefDate As Date

dRefDate = #11/28/2008#

query1 = "SELECT Ticker, Abs(Open-Close) AS OpCl, Abs(High-Low) AS HiLo, Open" & _
" FROM 5401.txt" & _
" WHERE dDate=" & CDateSQL(dRefDate) & _
" GROUP BY Ticker, Open-Close, High-Low, Open"

query2 = "SELECT TOP 1 Ticker AS YTicker, Close AS CloseYday" & _
" FROM 5401.txt" & _
" WHERE dDate<=" & CDateSQL(dRefDate) & _
" GROUP BY dDate, Ticker, Close" & _
" ORDER BY dDate DESC"
query3 = "SELECT Ticker, OpCl, HiLo, Abs(Open-CloseYday) AS ClOp" & _
" FROM (" & query1 & ") AS Q1 INNER JOIN (" & query2 & ") AS Q2" & _
" ON Q1.Ticker = Q2.YTicker"

sSQL = query3

SQL_ATR = sSQL
End Function


Now, how do I have to integrate the code I have put together so far and the function above? Sorry, I am still a beginner...

Best,
JapanFreak

You don't need the first bit of code. After you have copied the results of your query into excel you can use the following to return the max value:


x = application.max(workbooks("BookName").worksheets("SheetName").Range("A1:C5"))

debug.print x

Okay, thanks, but there is no way to include it into the SQL query? Because the result of that query should at the end of the day be the result of a sub-query which goes into another query...

No..........

Cannot you use an IIF??

Something like....
IIF(X>Y , IF( X>Z , X, Z ), IF ( Y>Z, Y, Z ) )

Baring the use of IIF, cause that can get complex fast...
You could try a Union query to find your max
Select max(value)
from ( select column1 as value from anywhere
union select column2 as value from anywhere)