Excel VBA - problems with subquery

Hi,

I am using ADO to retrieve records from an MS ACCESS DB in Excel. All my queries work fine but I am having problems with subqueries. My subqueries work fine in ACCESS but when I execute them via ADO I get the following error message:'"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

I hope someone can help me with the correct syntax. Alternatively I was thinking of creating a view in ACCESS but that seems not possible.

Help would much appreciated!

PHP Code: 

Sub retrieve_loan_details_test()

Dim Provider As String
Dim Source As String
Dim sConn As String
Dim sSql As String
Dim rep_per As Date
Dim LB, UB As Long

Dim cnPubs As ADODB.Connection
Dim rsPubs As ADODB.Recordset
Set cnPubs = New ADODB.Connection
Set rsPubs = New ADODB.Recordset

Provider = Range("Provider")
Source = Range("Source")
rep_per = Range("rep_per")
LB = "12"
UB = "18"

sConn = "PROVIDER=" & Provider & ";"
sConn = sConn & "DATA SOURCE=" & Source & ";Persist Security Info=False;"
cnPubs.Open sConn

sSql = "select c.bank, a.appID, a.month, b.min, sum(a.Mdeopn)"
sSql = sSql & " from Monthly_balances a, (SELECT appID, min(prepdate) as min FROM Prep_input where fullpartial = 'Full' group by appID) b, Loan_details c"
sSql = sSql & " where a.appID = b.appID"
sSql = sSql & " and a.appID = c.appID"
sSql = sSql & " and a.rep_per = #" & rep_per & "#"
sSql = sSql & " and a.month between " & LB & " and " & UB & ""
'sSql = sSql & " and DateDiff("m", #" & rep_per & "#", b.min) > 6"
sSql = sSql & " Group by c.bank, a.appID, a.month,b.min"


With rsPubs
    .ActiveConnection = cnPubs
    .Open sSql
    Range("tst").CopyFromRecordset rsPubs
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub 


Thanks for your swift response.

The Datediff function does indeed not work but I think I will be able to find an SQL alternative.

My main problem is the subquery; I have spend most of the day on searching for alternatives but didnt manage to find one.

Do I need to change the syntax for the subquery ((SELECT appID, min(prepdate) as min FROM Prep_input where fullpartial = 'Full' group by appID) b) or is there some other way to do this?

Thanks again for your help!

Thanks a lot, referring to a function was indeed the problem, it works now!

Hi , I am using subquery for Retrieve Next record date . My subqueries work fine in ACCESS and takes so long for excel in line .Range("A2").CopyFromRecordset rst
please help

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WBCurrent As Workbook
Dim WS As Worksheet
'Change ScreenUpdating, Calculation, EnableEvents, ....
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False

Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0;Readonly=False"";"
.Open
End With

Set WBCurrent = ActiveWorkbook
WBCurrent.Worksheets("SAP1").Rows("2:100000").Delete

'mold_code = "002073-00"
strSQL = "SELECT [All$A1:L].[mold code], [All$A1:L].row, [All$A1:L].date2, [All$A1:L].[machine code], " _
& "(SELECT top 1 temp.[date2] FROM [All$A1:L] AS temp " _
& "WHERE temp.[machine code] = [All$A1:L].[machine code] AND temp.row > [All$A1:L].row " _
& "ORDER BY temp.row ) AS PreviousValue " _
& "FROM [All$A1:L]; "



Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic
Set WS = WBCurrent.Worksheets("SAP1")
With WS
.Cells(1, 1) = "Machine Code"
.Cells(1, 2) = "Last Plan"
.Cells(1, 3) = "Mold Code"
.Range("A2").CopyFromRecordset rst
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlAutomatic
End With