Count in a Query

[Lauri]

I have a query that counts how many records meet a criteria.

Date Count: count(1)
Group by Expression
Criteria from a form field

The result gives me the total records for the specified date.

Now I need to use that number. When someone chooses a date on the form,
If count > 7 put a message on the screen.
somewhat like - if query![query name]![field name] > 7 then msgbox.
Evidently this way of recalling controls only works for forms and reports,
not queries.

How can a pass that query field to Visual Basic? a macro? an expression?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Recommendation: Don't use the word "Date" as a column name, it is an
VBA function "Date()" that shows as "Date" in queries. It's use can
cause confusion.

These solutions assume only 1 record will be returned, which I inferred
by your question.

====

You may be able to use a DCount() function. A text box w/ a
ControlSource property of (all one line - watch for line-wrap):

=IIf(DCount("*","TableName","DateColumn = #" & Me!txtCriteria &
"#")>7,"More than 7 records","")

The above assumes that TextBox txtCriteria holds a valid date.

You'll have to Requery the text box every time the txtCriteria changes
(use the txtCriteria's After_Update event procedure).

====

Or, you could use VBA & a Recordset. E.g. (air code):

' Change the Form and Table and Control names to fit
' your situation.

const SQL = "PARAMETERS Forms!FormName!ControlName Date;" & _
"SELECT Count(*) FROM TableName " & _
"WHERE DateColumn = Forms!FormName!ControlName"

dim db as dao.database
dim rs as dao.recordset
dim i as integer

set db = currentdb
set rs = db.openrecordset(SQL)

' Show results of query
if not rs.eof then
If rs(0) > 7 then
Me!txtResults = "More than 7 records"
else
Me!txtResults = ""
end if
else
Me!txtResults = ""
end if

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbnSIechKqOuFEgEQIlBwCfYgRARTxirZAa9VgheEVKGJnlVOsAnizm
mi0kam/3KmQdBHaDpoLoGk3p
=z17I
-----END PGP SIGNATURE-----

 

[fredg]

I have a query that counts how many records meet a criteria.

Date Count: count(1)
Group by Expression
Criteria from a form field

The result gives me the total records for the specified date.

Now I need to use that number. When someone chooses a date on the form,
If count > 7 put a message on the screen.
somewhat like - if query![query name]![field name] > 7 then msgbox.
Evidently this way of recalling controls only works for forms and reports,
not queries.

How can a pass that query field to Visual Basic? a macro? an expression?

Is that the only record returned by the query?
In VBA code you could use:

If DLookUp("[FieldName]","QueryName") > 7 Then
MsgBox "Some message here."
End If

Note: Count is not a good name to use as a field name.
Count is an Access/VBA reserved word.
See the appropriate KnowledgeBase article for your version of Access.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

 

[Lauri]

That was exactly the clue I needed! I was able to add that code to some
other pieces to make my form do exactly as the department has asked.

Thank you bunches.

-Lauri

I have a query that counts how many records meet a criteria.

Date Count: count(1)
Group by Expression
Criteria from a form field

The result gives me the total records for the specified date.

Now I need to use that number. When someone chooses a date on the form,
If count > 7 put a message on the screen.
somewhat like - if query![query name]![field name] > 7 then msgbox.
Evidently this way of recalling controls only works for forms and reports,
not queries.

How can a pass that query field to Visual Basic? a macro? an expression?

Is that the only record returned by the query?
In VBA code you could use:

If DLookUp("[FieldName]","QueryName") > 7 Then
MsgBox "Some message here."
End If

Note: Count is not a good name to use as a field name.
Count is an Access/VBA reserved word.
See the appropriate KnowledgeBase article for your version of Access.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

 

[SUB]

-----Original Message-----
I have a query that counts how many records meet a criteria.

Date Count: count(1)
Group by Expression
Criteria from a form field

The result gives me the total records for the specified date.

Now I need to use that number. When someone chooses a date on the form,
If count > 7 put a message on the screen.
somewhat like - if query![query name]![field name] > 7 then msgbox.
Evidently this way of recalling controls only works for forms and reports,
not queries.

How can a pass that query field to Visual Basic? a macro? an expression?

--
Lauri
Application Support Technician
Bellingham Public Schools
Microsoft Office Specialist Instructor


.

I think I have gathered what you are trying to do...

If I am correct, I would get your query so that it only
shows the final sum you need (possibly by grouping (SUM))
then on your form create a text box (or similar object)
and set its focus to the query with the single figure you
are interested in, you can reference this figure by using
me.textbox1.... etc..