returning more than one variable on Call retValue()

[Steven]

I am calling this function with:

All v.... variables are Dim As Double
Call retValues(vTestCo, vTestAcct, vTestDept, vTestPeriod, " Tables INNER
JOINED ", " WHERE ....... ")

Public Function retValues(ByRef vTestCo, ByRef vTestAcct, ByRef vTestDept,
ByRef vTestPeriod, 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
vTestCo = Nz(rs!vCount, 0)

MsgBox vTestCo

rs.Close
Set rs = Nothing

End Function

By doing what I have in the SELECT I am getting all the total records
returned on the vTestCo. That is not what I want. I am looking for 4
individual totals. The query below returns all the records at one time. It
came from buiding a new Query and taking the SQL. I want to return the
Count(*) for each of items individually in the WHERE clause. That is where I
get the vTestCo ... Acct ... Dept ... Period.

SELECT Batch09.*, Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active]) AS Expr3, Nz([Dept].[Status],0) AS Expr2,
Nz([C001],"X") AS Expr4
FROM (((Batch09 LEFT JOIN Co ON Batch09.Co = Co.Co) LEFT JOIN
ChartOfAccounts ON (Batch09.Account = ChartOfAccounts.Account) AND
(Batch09.Co = ChartOfAccounts.Co)) LEFT JOIN Dept ON Batch09.Account =
Dept.DeptNum) LEFT JOIN Period ON Batch09.Period = Period.Period
WHERE (((Nz([Co].[Status],0))=0)) OR (((Nz([ChartOfAccounts].[Active]))=0))
OR (((Nz([Dept].[Status],0))=0)) OR (((Nz([C001],"X"))<>"O"));

Thank you for your help.

Steven

 

[pietlinden]

I am calling this function with:

All v....  variables are Dim As Double
Call retValues(vTestCo, vTestAcct, vTestDept, vTestPeriod, " Tables INNER
JOINED  ", " WHERE ....... ")

Public Function retValues(ByRef vTestCo, ByRef vTestAcct, ByRef vTestDept,
ByRef vTestPeriod, 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
     vTestCo = Nz(rs!vCount, 0)

     MsgBox vTestCo

     rs.Close
     Set rs = Nothing

End Function

By doing what I have in the SELECT I am getting all the total records
returned on the vTestCo.  That is not what I want.  I am looking for 4
individual totals.  The query below returns all the records at one time..  It
came from buiding a new Query and taking the SQL.  I want to return the
Count(*) for each of items individually in the WHERE clause.  That is where I
get the vTestCo ... Acct ... Dept ... Period.  

SELECT Batch09.*, Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active]) AS Expr3, Nz([Dept].[Status],0) AS Expr2,
Nz([C001],"X") AS Expr4
FROM (((Batch09 LEFT JOIN Co ON Batch09.Co = Co.Co) LEFT JOIN
ChartOfAccounts ON (Batch09.Account = ChartOfAccounts.Account) AND
(Batch09.Co = ChartOfAccounts.Co)) LEFT JOIN Dept ON Batch09.Account =
Dept.DeptNum) LEFT JOIN Period ON Batch09.Period = Period.Period
WHERE (((Nz([Co].[Status],0))=0)) OR (((Nz([ChartOfAccounts].[Active]))=0))
OR (((Nz([Dept].[Status],0))=0)) OR (((Nz([C001],"X"))<>"O"));

Thank you for your help.

Steven

so return a recordset. BTW, you don't CALL a function usually... you
set it equal to something.

e.g.
Dim strReturnValueString as string

strReturnValueString = retValues(vTestCo, vTestAcct, vTestDept,
vTestPeriod, " Tables INNER

JOINED ", " WHERE ....... ")

Hmm... why not just use a parameter query to get your values and pass
in the parameters. Then open the recordset based on it, then use the
GetString method of the recordset object to turn the thing into a
delimited string. No muss, no fuss, and all the logic would be
encapsulated in your stored procedure/query.

(Okay, go ahead Aaron... this is where you tell him how cool ADO is...)

 

[pietlinden]

I am calling this function with:

All v....  variables are Dim As Double
Call retValues(vTestCo, vTestAcct, vTestDept, vTestPeriod, " Tables INNER
JOINED  ", " WHERE ....... ")

Public Function retValues(ByRef vTestCo, ByRef vTestAcct, ByRef vTestDept,
ByRef vTestPeriod, 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
     vTestCo = Nz(rs!vCount, 0)

     MsgBox vTestCo

     rs.Close
     Set rs = Nothing

End Function

By doing what I have in the SELECT I am getting all the total records
returned on the vTestCo.  That is not what I want.  I am looking for 4
individual totals.  The query below returns all the records at one time..  It
came from buiding a new Query and taking the SQL.  I want to return the
Count(*) for each of items individually in the WHERE clause.  That is where I
get the vTestCo ... Acct ... Dept ... Period.  

SELECT Batch09.*, Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active]) AS Expr3, Nz([Dept].[Status],0) AS Expr2,
Nz([C001],"X") AS Expr4
FROM (((Batch09 LEFT JOIN Co ON Batch09.Co = Co.Co) LEFT JOIN
ChartOfAccounts ON (Batch09.Account = ChartOfAccounts.Account) AND
(Batch09.Co = ChartOfAccounts.Co)) LEFT JOIN Dept ON Batch09.Account =
Dept.DeptNum) LEFT JOIN Period ON Batch09.Period = Period.Period
WHERE (((Nz([Co].[Status],0))=0)) OR (((Nz([ChartOfAccounts].[Active]))=0))
OR (((Nz([Dept].[Status],0))=0)) OR (((Nz([C001],"X"))<>"O"));

Thank you for your help.

Steven

Oh, to answer your question, though, you declare a bunch of variables
in your routine, and then you call them By Reference instead of By
Value. Then because the *memory addresses* of the variables you pass
into the function are modifiable, you can change a bunch in your
routine and pass them back out. Otherwise, you would have to declare
your function to return a variant and return an array of some type (or
a recordset).

 

[Steven]

I think in your first response that is what I am trying to do. I am trying
to use the Call retValues(... , ... , ...) to do it.


Here is my issue: I may end up with the following in the
OpenRecordset(strSQL):

Co Acct Dept Date Amt CoOpen AcctOpen DeptOpen
PerOpen
Co1 1000 2000 07/01/08 1.00 -1 -1 -1
-1
Co1 4000 1000 07/01/08 1.00 -1 -0 -1
-1
Co1 6000 2000 07/01/08 1.00 -1 -1 -1
-1
Co1 7000 3000 07/01/08 1.00 -1 -0 -1
-1
Co1 8000 4000 07/01/08 -4.00 -1 -0 0
-1

Note way down below in this post that (Nz([C001],"X"))<>"O") is actually the
PerOpen testing being done. I just say that because all the others have
naming convention that is obvious to see that is what they are.

Based on the returned data above I want to return the count of the 0's in the:
Public Function retValues( , , , , )
The result from above would be:
vTestCo = 0
vTestAcct = 3
vTestDept = 1
vTestPeriod = 0

Basically saying the the entry cannot be posted because it has Accounts and
Departments not passing the test in the function. See the: vTestCo =
Nz(rs!vCount, 0) in the Public Function retValues( , , , , ) below : that is
where I want to be able to return the vTestCo , Acct, Dept, Period results
that are illustrated above.

I dont know how to get these multiple returns to work in further
developement of that function, but I think all the information is sitting
right there.

Thank you for your continued help.

Steven

I am calling this function with:

All v.... variables are Dim As Double
Call retValues(vTestCo, vTestAcct, vTestDept, vTestPeriod, " Tables INNER
JOINED ", " WHERE ....... ")

Public Function retValues(ByRef vTestCo, ByRef vTestAcct, ByRef vTestDept,
ByRef vTestPeriod, 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
vTestCo = Nz(rs!vCount, 0)

MsgBox vTestCo

rs.Close
Set rs = Nothing

End Function

By doing what I have in the SELECT I am getting all the total records
returned on the vTestCo. That is not what I want. I am looking for 4
individual totals. The query below returns all the records at one time.. It
came from buiding a new Query and taking the SQL. I want to return the
Count(*) for each of items individually in the WHERE clause. That is where I
get the vTestCo ... Acct ... Dept ... Period.

SELECT Batch09.*, Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active]) AS Expr3, Nz([Dept].[Status],0) AS Expr2,
Nz([C001],"X") AS Expr4
FROM (((Batch09 LEFT JOIN Co ON Batch09.Co = Co.Co) LEFT JOIN
ChartOfAccounts ON (Batch09.Account = ChartOfAccounts.Account) AND
(Batch09.Co = ChartOfAccounts.Co)) LEFT JOIN Dept ON Batch09.Account =
Dept.DeptNum) LEFT JOIN Period ON Batch09.Period = Period.Period
WHERE (((Nz([Co].[Status],0))=0)) OR (((Nz([ChartOfAccounts].[Active]))=0))
OR (((Nz([Dept].[Status],0))=0)) OR (((Nz([C001],"X"))<>"O"));

Thank you for your help.

Steven

Oh, to answer your question, though, you declare a bunch of variables
in your routine, and then you call them By Reference instead of By
Value. Then because the *memory addresses* of the variables you pass
into the function are modifiable, you can change a bunch in your
routine and pass them back out. Otherwise, you would have to declare
your function to return a variant and return an array of some type (or
a recordset).

 

[Steven]

Here is what I ended up with and it works. I ended up just stepping through
the recordset.

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 Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active],0) AS Expr2, Nz([Dept].[Status],0) AS Expr3,
Nz([C001],'X') AS Expr4 " & _
"FROM " & TableName & " " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst

If rs.BOF = True And rs.EOF = True Then
MsgBox "All ok" 'In actual production do not show MsgBox
Else
Dim v1 As Double
Dim v2 As Double
Dim v3 As Double
Dim v4 As Double

Do Until rs.EOF = True
If Nz(rs("Expr1"), 0) = 0 Then v1 = v1 + 1
If Nz(rs("Expr2"), 0) = 0 Then v2 = v2 + 1
If Nz(rs("Expr3"), 0) = 0 Then v3 = v3 + 1
If Nz(rs("Expr4"), 0) <> "O" Then v4 = v4 + 1
rs.MoveNext
Loop
End If

'In actual production do not show MsgBox'es
MsgBox v1
MsgBox v2
MsgBox v3
MsgBox v4

rs.Close
Set rs = Nothing

Macro11_Exit:
Exit Function

Macro11_Err:
MsgBox Error$
Resume Macro11_Exit

End Function


Is this an ok approach or is there a much more preferred method? Thank you
for all your instruction and guidance and resolutions.


Steven

I think in your first response that is what I am trying to do. I am trying
to use the Call retValues(... , ... , ...) to do it.


Here is my issue: I may end up with the following in the
OpenRecordset(strSQL):

Co Acct Dept Date Amt CoOpen AcctOpen DeptOpen
PerOpen
Co1 1000 2000 07/01/08 1.00 -1 -1 -1
-1
Co1 4000 1000 07/01/08 1.00 -1 -0 -1
-1
Co1 6000 2000 07/01/08 1.00 -1 -1 -1
-1
Co1 7000 3000 07/01/08 1.00 -1 -0 -1
-1
Co1 8000 4000 07/01/08 -4.00 -1 -0 0
-1

Note way down below in this post that (Nz([C001],"X"))<>"O") is actually the
PerOpen testing being done. I just say that because all the others have
naming convention that is obvious to see that is what they are.

Based on the returned data above I want to return the count of the 0's in the:
Public Function retValues( , , , , )
The result from above would be:
vTestCo = 0
vTestAcct = 3
vTestDept = 1
vTestPeriod = 0

Basically saying the the entry cannot be posted because it has Accounts and
Departments not passing the test in the function. See the: vTestCo =
Nz(rs!vCount, 0) in the Public Function retValues( , , , , ) below : that is
where I want to be able to return the vTestCo , Acct, Dept, Period results
that are illustrated above.

I dont know how to get these multiple returns to work in further
developement of that function, but I think all the information is sitting
right there.

Thank you for your continued help.

Steven

I am calling this function with:

All v.... variables are Dim As Double
Call retValues(vTestCo, vTestAcct, vTestDept, vTestPeriod, " Tables INNER
JOINED ", " WHERE ....... ")

Public Function retValues(ByRef vTestCo, ByRef vTestAcct, ByRef vTestDept,
ByRef vTestPeriod, 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
vTestCo = Nz(rs!vCount, 0)

MsgBox vTestCo

rs.Close
Set rs = Nothing

End Function

By doing what I have in the SELECT I am getting all the total records
returned on the vTestCo. That is not what I want. I am looking for 4
individual totals. The query below returns all the records at one time.. It
came from buiding a new Query and taking the SQL. I want to return the
Count(*) for each of items individually in the WHERE clause. That is where I
get the vTestCo ... Acct ... Dept ... Period.

SELECT Batch09.*, Nz([Co].[Status],0) AS Expr1,
Nz([ChartOfAccounts].[Active]) AS Expr3, Nz([Dept].[Status],0) AS Expr2,
Nz([C001],"X") AS Expr4
FROM (((Batch09 LEFT JOIN Co ON Batch09.Co = Co.Co) LEFT JOIN
ChartOfAccounts ON (Batch09.Account = ChartOfAccounts.Account) AND
(Batch09.Co = ChartOfAccounts.Co)) LEFT JOIN Dept ON Batch09.Account =
Dept.DeptNum) LEFT JOIN Period ON Batch09.Period = Period.Period
WHERE (((Nz([Co].[Status],0))=0)) OR (((Nz([ChartOfAccounts].[Active]))=0))
OR (((Nz([Dept].[Status],0))=0)) OR (((Nz([C001],"X"))<>"O"));

Thank you for your help.

Steven

Oh, to answer your question, though, you declare a bunch of variables
in your routine, and then you call them By Reference instead of By
Value. Then because the *memory addresses* of the variables you pass
into the function are modifiable, you can change a bunch in your
routine and pass them back out. Otherwise, you would have to declare
your function to return a variant and return an array of some type (or
a recordset).