469,267 Members | 1,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

displaying record count in a message box

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
5 19723
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
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
<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
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
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.

Similar topics

2 posts views Thread by Deamond | last post: by
3 posts views Thread by thomasp | last post: by
6 posts views Thread by Kulwinder Sayal | last post: by
4 posts views Thread by Peter W Johnson | last post: by
2 posts views Thread by John | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.