Thank you for responding.
Here is retValues function and here is what the strSql will be in the
function.
strSQL will be :
SELECT Count(*) As vCount FROM Batch09 LEFT JOIN Co ON (Batch09.Co = Co.Co)
LEFT JOIN ChartOfAccounts ON ((Batch09.Co = ChartOfAccounts.Co) AND
(Batch09.Account = ChartOfAccounts.Account)) WHERE Nz([Co].[Status],0)=0
On the: Set rs = CurrentDb.OpenRecordset(strSQL) 'It errors here saying
missing operand.
Is it because it has more than one LEFT JOIN ?
--------------------------------------------------------------------------
Public Function retValues(ByRef vTestCo, TableName As String, WhereClause As
String) As String
On Error GoTo Macro11_Err
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Count(*) As vCount " & _
"FROM " & TableName & " " & _
"WHERE " & WhereClause
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
vTestCo = Nz(rs!vCount, 0)
MsgBox vTestCo
rs.Close
Set rs = Nothing
Macro11_Exit:
Exit Function
Macro11_Err:
MsgBox Error$
Resume Macro11_Exit
End Function
Thank you for your help in solving this.
Steven
I get a missing operand message with the following:
Call retValues(vTestCo, CurrentBatch & " LEFT JOIN Co ON (" & CurrentBatch &
".Co = Co.Co) LEFT JOIN ChartOfAccounts ON ((" & CurrentBatch & ".Co =
ChartOfAccounts.Co) AND (" & CurrentBatch & ".Account =
ChartOfAccounts.Account))", "Nz([Co].[Status],0)=0")
What is wrong?
Thank you,
Steven
include a line in your code to print the SQL statement to the debug
window. Then copy and paste it into a blank query and run it. Does
it work?
What does the signature of retValues look like?