Jeff Queries are like below:
SELECT Min(MailDate) FROM YourTable
What is the oldest maildate with an operator assigned to it.
SELECT Min(MailDate) FROM YourTable WHERE Operator is Not Null
What is the oldest maildate without an operator assigned to it
SELECT Max(MailDate) FROM YourTable WHERE Operator is Null
What is the oldest maildate for the current year
SELECT Min(MailDate) FROM YourTable WHERE [Year] = "07"
What is the oldest maildate for the prior years(anything that is not
SELECT Min(MailDate) FROM YourTable WHERE [Year] <> "07"
What is the oldest maildate where data in CURRENT starts with 714.
SELECT Min(MailDate) FROM YourTable WHERE [CURRENT] Like "714*"
, How many records are between 0 to 30 days with an operator.
SELECT Count(*) FROM YourTable WHERE [Days] Between 0 and 30
and
Operator is not Null
If there is a way to combine any of the queries - this will make
things more efficient for you.
That being said, I don't particularly like having 30+ single-use
objects in my databases. I would write a function to return a scalar
and bind the calling of that function to a control in your report.
What I mean is this:
'Call this function like so: =getMyScalar("Count(*)", "YourTable",
"[Days] Between 0 and 30"
Public Function getMyScalar(strAggregate as String, strTable as
String, optional strWhere as String) as Variant
'Some Variables
dim rst as New ADODB.Recordset
dim strSql as String
strSql = "Select " & strAggregate & " From " & strTable
'Fix the where clause - "LIKE" bombs w/ vba - use ALIKE instead.
'After fixing, append to strSql
if strWhere <> "" then
if instr(1, strWhere, "like") <> 0 then
strWhere = Replace(strWhere, "alike", "like")
strWhere = Replace(strWhere, "like", "alike")
strWhere = Replace(strWhere, "*", "%")
strWhere = Replace(strWhere, "?", "_")
'If you use other wildcards in your "like" comparisons, you will
want to address them here too.
end if
strSql = strSql & " Where " & strWhere
end if
'Open the query and return the scalar
rst.Open strSql, currentProject.connection
if not rst.eof then
getMyScalar = rst(0)
end if
'Cleanup
if not rst is nothing then
if rst.state = adstateopen then rst.close
set rst = nothing
end if
end function
Can you create a report with no recordsource? I guess I've never
tried. . . You could call this function from many textboxes and have
the single scalar show on your report. This is still not efficient (as
it will execute a single query for every text box) but it saves you
some clutter.
PS. You will probably want to save yourself some hassle and remove my
"LIKE" re-formatting. If that is the case, just make sure that the
strWhere you pass in uses ALIKE with the standard SQL wildcards.
-Kris
