SQL retValue(.....,....,....)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top