Count in a Query

  • Thread starter Thread starter Lauri
  • Start date Start date
L

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?
 
-----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-----
 
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?
Click to expand...

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'
 
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


fredg said:
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?
Click to expand...

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'
Click to expand...
 
-----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


.
Click to expand...



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..
 
Back
Top