S
Steven
I want to run a query where it will return a value which will be a test value
to continue or Exit Sub. I want to do it with an SQL statement. I have done
some easier versions for example: Note I am leaving out the On Error
statements:
Sub TestAccounts()
Dim CurrentBatch As String
CurrentBatch = "Batch10"
Dim vTestAcctValid As Double
Call retInvalidAccts(vTestAcctValid, CurrentBatch & " LEFT JOIN
ChartOfAccounts ON (" & CurrentBatch & ".Account = ChartOfAccounts.Account)
AND (" & CurrentBatch & ".Co = ChartOfAccounts.Co)",
"IsNull(ChartOfAccounts.Account) = -1")
If vTestAcctValid > 0 Then
DoCmd.RunSQL "UPDATE PostingTable SET Posting = 0;"
MsgBox "Batch not able to be posted." + Chr(13) + Chr(13) + _
"Batch has invalid accounts."
Exit Sub
End If
End Sub
Public Function retInvalidAccts(ByRef vTestAcctValid, TableName As String,
WhereClause As String) As String
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
vTestAcctValid = Nz(rs!vCount, 0)
rs.Close
Set rs = Nothing
End Function
Works fine. Now: I have a much more complex query becuase it combines
queries that have created fields and builds a final query to do the testing.
This is getting more complex for me.
Note below that Batch09 is what I am calling variable CurrentBatch above,
but I have taken the Batch09 (CurrentBatch) and built intermediate queries
and want to use them in a more complex final test query.
Here is the code out of the Query Design Builder Tab that works:
SELECT [sqryChartCo+Account].Active, Co.Status, Dept.Status,
[sqryBatch09Co+Acct].*
FROM ((Co INNER JOIN [sqryBatch09Co+Acct] ON Co.Co =
[sqryBatch09Co+Acct].Co) INNER JOIN [sqryChartCo+Account] ON
[sqryBatch09Co+Acct].Expr1 = [sqryChartCo+Account].Expr1) INNER JOIN Dept ON
[sqryBatch09Co+Acct].Expr2 = Dept.DeptNum
WHERE ((([sqryChartCo+Account].Active)=0)) OR (((Co.Status)=0)) OR
(((Dept.Status)=0));
See how I taking queries and adding them to make another query and JOINING
fields.
Here is sqryBatch09Co+Acct:
SELECT Batch09.*, [Co]+[Account] AS Expr1, Right([Account],4) AS Expr2
FROM Batch09;
Here is the sqryChartCo+Account:
SELECT ChartOfAccounts.*, [Co]+[Account] AS Expr1
FROM ChartOfAccounts;
How do I combine all this into a working SQL statement and just return an
count like I am doing in the top example.
Thank you very much for your help.
Steven
to continue or Exit Sub. I want to do it with an SQL statement. I have done
some easier versions for example: Note I am leaving out the On Error
statements:
Sub TestAccounts()
Dim CurrentBatch As String
CurrentBatch = "Batch10"
Dim vTestAcctValid As Double
Call retInvalidAccts(vTestAcctValid, CurrentBatch & " LEFT JOIN
ChartOfAccounts ON (" & CurrentBatch & ".Account = ChartOfAccounts.Account)
AND (" & CurrentBatch & ".Co = ChartOfAccounts.Co)",
"IsNull(ChartOfAccounts.Account) = -1")
If vTestAcctValid > 0 Then
DoCmd.RunSQL "UPDATE PostingTable SET Posting = 0;"
MsgBox "Batch not able to be posted." + Chr(13) + Chr(13) + _
"Batch has invalid accounts."
Exit Sub
End If
End Sub
Public Function retInvalidAccts(ByRef vTestAcctValid, TableName As String,
WhereClause As String) As String
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
vTestAcctValid = Nz(rs!vCount, 0)
rs.Close
Set rs = Nothing
End Function
Works fine. Now: I have a much more complex query becuase it combines
queries that have created fields and builds a final query to do the testing.
This is getting more complex for me.
Note below that Batch09 is what I am calling variable CurrentBatch above,
but I have taken the Batch09 (CurrentBatch) and built intermediate queries
and want to use them in a more complex final test query.
Here is the code out of the Query Design Builder Tab that works:
SELECT [sqryChartCo+Account].Active, Co.Status, Dept.Status,
[sqryBatch09Co+Acct].*
FROM ((Co INNER JOIN [sqryBatch09Co+Acct] ON Co.Co =
[sqryBatch09Co+Acct].Co) INNER JOIN [sqryChartCo+Account] ON
[sqryBatch09Co+Acct].Expr1 = [sqryChartCo+Account].Expr1) INNER JOIN Dept ON
[sqryBatch09Co+Acct].Expr2 = Dept.DeptNum
WHERE ((([sqryChartCo+Account].Active)=0)) OR (((Co.Status)=0)) OR
(((Dept.Status)=0));
See how I taking queries and adding them to make another query and JOINING
fields.
Here is sqryBatch09Co+Acct:
SELECT Batch09.*, [Co]+[Account] AS Expr1, Right([Account],4) AS Expr2
FROM Batch09;
Here is the sqryChartCo+Account:
SELECT ChartOfAccounts.*, [Co]+[Account] AS Expr1
FROM ChartOfAccounts;
How do I combine all this into a working SQL statement and just return an
count like I am doing in the top example.
Thank you very much for your help.
Steven