Using DCOUNT in ACCESS 2002 VBA

[13badar]

Hi, I wanted to save the following Query, but only wanted to save Queries with Data in them since some of these Queries will have no Data in them. I wanted to save the Queries with DATA in them. Could you let me know what Argument should I be using in my DCount Statement??

SQL = "SELECT A.CID, A.month, A.contractyear, "
SQL = SQL & "A.BranchName, B.Report"
SQL = SQL & " FROM tbl1 AS B INNER JOIN tbl2 AS A"
SQL = SQL & " ON B.BranchName = A.BranchName"
SQL = SQL & " WHERE B.Report='" & ReportType & "'"
SQL = SQL & " and A.BranchName=" & MSA_SA(Iteration)
SQL = SQL & " Order by A.CID"

Set db = CurrentDb
If DCount("*", SQL) > 0 Then
db.CreateQueryDef MSA_SA & " QUERY", SQL
End If
On Error Resume Next

 

[Zion7]

strQuery = "SELECT A.CID, A.month, A.contractyear, " & _
"A.BranchName, B.Report " & _
"FROM tbl1 AS B INNER JOIN tbl2 AS A " & _
"ON B.BranchName = A.BranchName"


IF DCount("pkID","strQuery ","BranchName ='" & MSA_SA(Iteration) & "'")<> 0 Then...

What is "MSA_SA(Iteration)"?
Is it an array? Is it a string, numeric, or Date datatype?

...very important!

 

[Zion7]

forgot...

IF DCount("pkID","strQuery ","Report='" & ReportType & "' AND BranchName ='" & MSA_SA(Iteration) & "'")<> 0 Then...

 

[13badar]

Thanx Zion7 for your help.
I've a Question:
I have this Array:
MSA_ARRAY = Array("Altamont", "Bayfung", "SilverCreek" _
"Blossom", "CanyonLakes", "Valle", "EdenPalms")

I want to call values from a cloumn named "SA" from a Table named "tblRegion". What should be the Syntax for that.

MSA_ARRAY = Array(Object!Table!tblRegion!SA)

When I run the above statement, Im getting an error: Variable not defined and OBJECT is highlighted. Should I DIM all the variables in the parenthesis??

 

[Zion7]

Dim rec As ADODB.Recordset

Set rec = New ADODB.Recordset

MSA_ARRAY = rec.GetRows(,"SA")

....

rec.Close: Set rec = Nothing

This will fill your array....

Then, maybe use the recordCount property, to assign the # of iterations...