Counting Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there an easy way to display, on a form, the number of records with the
same date as the date control on that form? If I have an unbound text box
called txtCount, how could I count the records behind the form using the
criteria in the txtDate control? Thanks...
 
Is there an easy way to display, on a form, the number of records with the
same date as the date control on that form? If I have an unbound text box
called txtCount, how could I count the records behind the form using the
criteria in the txtDate control? Thanks...
Click to expand...

=DCount("*","TableName","[DateFieldName] = #" & [txtDate] & "#")
 
Put this in the control source of txtCount
=Dcount("*","MyTable", "[DateField] = #" & txtDate & "#")

In most cases a DCount will execute faster if you use "*" rather than a
specif fieldname to count.
 
Hi Don,

try;

=DCount("RecordID","UnderlyingTable/QueryNameHere","DateFieldName = #" &
Me.txtDate & "#")

or similar in the After Update event of the field that selects the current
record.

hope this helps,

TonyT..
 
You can do something like this with the DCount() function:

txtCount = DCount("*","
","[DateField]=#" & Me.txtDate & "#")

However, this gets complicated if you want only records pertaining to a form
because of filters. You can replace "Table" with [Forms]![FormName].Form.
RecordSource, but that might not help if your users apply filters to the form.
 
Not only that, but if you select a field name, DCount only counts the rows
where that field is non-null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
Put this in the control source of txtCount
=Dcount("*","MyTable", "[DateField] = #" & txtDate & "#")

In most cases a DCount will execute faster if you use "*" rather than a
specif fieldname to count.

Don said:
Is there an easy way to display, on a form, the number of records with
the
same date as the date control on that form? If I have an unbound text
box
called txtCount, how could I count the records behind the form using the
criteria in the txtDate control? Thanks...
Click to expand...
Click to expand...
 
Don said:
Is there an easy way to display, on a form, the number of records with the
same date as the date control on that form? If I have an unbound text box
called txtCount, how could I count the records behind the form using the
criteria in the txtDate control? Thanks...
Click to expand...


You got four replies all suggesting the essentially the same
thing, but be aware the those will only work if the system
is set to use US style dates. If there is any chance that
one of your users may use a different date setting, then you
should use:

.... ,"[DateFieldName]=" & Format(txtDate,"\#m\/d\/yyyy\#")
 
Back
Top