By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,583 Members | 3,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,583 IT Pros & Developers. It's quick & easy.

displaying record count in a message box

P: n/a
Hi,

I need to be able to display a count of records in a message box but
with a criteria of the just ones where there is a yes in a certain
field. so the message box will pop up saying something like "there are
23 forms issued"

I've got no idea how to do this. been reading about dcount and
experimenting with it but getting no where. Can you help??

Paul

Mar 1 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
LTotal = DCount("SomeField", "SomeTable", "SomeOtherField = 'Yes'")

Msgbox "There are " & LTotal & " forms issued"

Please remember to thank those who help you, as so few others have.

On Mar 1, 10:38 am, Marshall...@gmail.com wrote:
Hi,

I need to be able to display a count of records in a message box but
with a criteria of the just ones where there is a yes in a certain
field. so the message box will pop up saying something like "there are
23 forms issued"

I've got no idea how to do this. been reading about dcount and
experimenting with it but getting no where. Can you help??

Paul

Mar 1 '07 #2

P: n/a
On 1 Mar, 16:04, "ManningFan" <manning...@gmail.comwrote:
LTotal = DCount("SomeField", "SomeTable", "SomeOtherField = 'Yes'")

Msgbox "There are " & LTotal & " forms issued"

Please remember to thank those who help you, as so few others have.

On Mar 1, 10:38 am, Marshall...@gmail.com wrote:
Hi,
I need to be able to display a count of records in a message box but
with a criteria of the just ones where there is a yes in a certain
field. so the message box will pop up saying something like "there are
23 forms issued"
I've got no idea how to do this. been reading about dcount and
experimenting with it but getting no where. Can you help??
Paul- Hide quoted text -

- Show quoted text -

Thanks! That works but its a little slow, takes around 2 mins to show
the message box. Any ideas why?

Mar 1 '07 #3

P: n/a
<Ma*********@gmail.comwrote in message
news:11**********************@30g2000cwc.googlegro ups.com...
Hi,

I need to be able to display a count of records in a message box but
with a criteria of the just ones where there is a yes in a certain
field. so the message box will pop up saying something like "there are
23 forms issued"

I've got no idea how to do this. been reading about dcount and
experimenting with it but getting no where. Can you help??

Paul
Something like this (untested):

Dim intCount As Integer
intCount = DCount("FieldName","tblMyTable","[MyField] = 'Yes'")
MsgBox "There are " & intCount & " forms issued."

HTH - Keith.
www.keithwilby.com
Mar 1 '07 #4

P: n/a
Hi Paul,

Your scenario is a little vague, so I will set up a scenario. On a
standard form you have a button. In the button click event you will
display a messagebox that will show the count of rows from tbl1 where
the YesNo field is yes.

Private Sub command1_click()
Dim i As Integer
i = Dcount("YesNo", "tbl1", "YesNo = -1)
MsgBox "The count of rows where YesNo is Yes is " & i
End Sub

Note: For YesNo fields the actual values are either -1 (=Yes) and 0
(=No)

In the Dcount function you first enter the field you want to query and
surround it with double quotes - in our case it is the YesNo field, then
enter the table which contains this field and surrount it by double
quotes. Then enter the critieria. In this case you want to count the
rows where the value of the YesNo field is -1 (Yes) and surround the
criteria with double quotes.

Now you can click your button and bring up the messagebox with the count
of rows.
MsgBox "The Count of rows from tbl1 where YesNo is yes is " & i
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Mar 1 '07 #5

P: n/a
Basically, it's going record by record and checking to see if it
should be included in the DCount. My guess is that you need to put an
index on the table. That should speed it up a little.

On Mar 1, 11:16 am, Marshall...@gmail.com wrote:
Thanks! That works but its a little slow, takes around 2 mins to show
the message box. Any ideas why?

Mar 1 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.