Speeding up code

[DZ]

Hello

I am using loops to create aggregate functions. The results of the agregate
function are concatenated into a strings that are used as a row source for a
list box.

What I need help with is. Can some tell me where I should replace certain
parts of the loop expressions with variables to speed things up. or can
anyone tell me any other ways to speed up these calcultation. As you can see,
I am using refrences to controls on the form as opposed to variables. Are
references to controls slower than variables?

When I trigger the loop, it takes a few seconds to see the results. It would
be better if the results were instantaneous or at least faster.

Thanks for any ideas

 

[DZ]

Here's the code

Do

If IsNull(rs(Me.lstTables)) Then
Else


If tdImportedTable.Fields(rs(Me.lstTables)).Type =
dbText Or _
tdImportedTable.Fields(rs(Me.lstTables)).Type = dbDate
Then

strNotNullValues = strNotNullValues & ";" &
rs(Me.lstTables) & ";" & _
tCount("[" & rs(Me.lstTables) & "]", Me.lstTables, "[" &
rs(Me.lstTables) & "] <> null")


Else
strNotNullValues = strNotNullValues & ";" &
rs(Me.lstTables) & ";" & _
tCount("[" & rs(Me.lstTables) & "]", Me.lstTables, "[" &
rs(Me.lstTables) & "] <> 0")

End If

End If

rs.MoveNext

Loop Until rs.EOF

'================================================================ Another loop

Do

If IsNull(rs(Me.lstTables)) Then
Else


Select Case rs("DataType")


Case "Text"

strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> null ") _
& ";" & rs(strMasterDataSheet)
'MsgBox
"[" & strFieldThatIdentifiesTable & "] = '" & Me.lstTables & "' AND [" &
"Master Data Sheet" & "] <> null "
Case "Number"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> 0 ") _
& ";" & rs(strMasterDataSheet)


'"[ShipCountry] = 'UK' AND [ShippedDate] >
#1-1-95#"
Case "Currency"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> 0 ") _
& ";" & rs(strMasterDataSheet)
Case "Date"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> null ") _
& ";" & rs(strMasterDataSheet)


End Select

End If

rs.MoveNext

Loop Until rs.EOF

 

[OldPro]

Here's the code

Do

If IsNull(rs(Me.lstTables)) Then
Else

If tdImportedTable.Fields(rs(Me.lstTables)).Type =
dbText Or _
tdImportedTable.Fields(rs(Me.lstTables)).Type = dbDate
Then

strNotNullValues = strNotNullValues & ";" &
rs(Me.lstTables) & ";" & _
tCount("[" & rs(Me.lstTables) & "]", Me.lstTables, "[" &
rs(Me.lstTables) & "] <> null")

Else
strNotNullValues = strNotNullValues & ";" &
rs(Me.lstTables) & ";" & _
tCount("[" & rs(Me.lstTables) & "]", Me.lstTables, "[" &
rs(Me.lstTables) & "] <> 0")

End If

End If

rs.MoveNext

Loop Until rs.EOF

'================================================================ Another loop

Do

If IsNull(rs(Me.lstTables)) Then
Else

Select Case rs("DataType")

Case "Text"

strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> null ") _
& ";" & rs(strMasterDataSheet)
'MsgBox
"[" & strFieldThatIdentifiesTable & "] = '" & Me.lstTables & "' AND [" &
"Master Data Sheet" & "] <> null "
Case "Number"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> 0 ") _
& ";" & rs(strMasterDataSheet)

'"[ShipCountry] = 'UK' AND [ShippedDate] >
#1-1-95#"
Case "Currency"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> 0 ") _
& ";" & rs(strMasterDataSheet)
Case "Date"
strNotNullValuesInOutputQ = strNotNullValuesInOutputQ &
";" & _
tCount("[" & rs(strMasterDataSheet) & "]", "OutputQ",
"[" & Me.cboFields & "] = '" & Me.lstTables & "' AND [" & rs("Master Data
Sheet") & "] <> null ") _
& ";" & rs(strMasterDataSheet)

End Select

End If

rs.MoveNext

Loop Until rs.EOF

I don't know if it's any faster, but I would not involve form
controls. I'd look at the tables directly, like this:
Dim db as dao.database
Dim rs as dao.recordset
set db = currentdb()
set rs = db.openrecordset("tblSomeTable",dbOpenTable)
rs.movefirst
With rs
While not .eof
' Put your comparison code here
.MoveNext
Wend
End With
rs.close
set rs=nothing
db.close
set db=nothing

If a filter is being used, then replace 'tblSomeTable" in the above
code to the proper SQL clause. If more than one table is referenced
in the clause, then use dbOpenDynaSet instead.

 

[Tony Toews [MVP]]

Here's the code

I'm not going to wade through your code as the news reader software
formatted it very ugly.

The key thing is to use aggregate queries whenever possible. So can
you do counts of the various fields. Using something like the
following query.

SELECT Sum(Abs(Not IsNull([textfield]))) AS Expr1, Sum(Abs(Not
IsNull([number]))) AS Expr2
FROM Table1;

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/