DCount syntax

[Guest]

Hi
I would really appreciate if someone could direct me towards a good
reference on DLookup/DCount usage and syntax. It seems failry straightforward
command but when it involves combination of criteria, simply fails. The
following is an example where I am stuck and would really appreciate input
from you guys.

I have a table named "tblAdvEv" in which are recorded adverse events for
every "Subject code" which may or may not be serious. This is recorded with a
Yes/No tickbox. On a subform "sfmVisitstatus" (on the parent form [Main
details]) there is a field which is updated with the On Current event of the
subform. I am trying get two pieces of info from the tblAdvEv table.

1. How many Adv events were encountered for each Subject code
2. How many Serious Adverse Events were encountered for each Subject code

The code which achieves counting (1) works perfectly
'Update AE count
str1 = "[Subject code]='" & Me.Subject_code & "'"
str = DCount("[Subject code]", "tblAdvEv", str1)
Me.SECount = str

But when I try to combine two criteria Subject code and [Serious Adverse
Event?]=True, it fails giving a type mismatch error:

'Update SAE count
str1 = "[Subject code]='" & Me.Subject_code & "'"
str2 = "[Serious Adverse Events?]=-1"
str = DCount("[Subject code]", "tblAdvEv", str1 And str2)
Me.Serious = str

I have tried different things: using criteria directly into the DCount
statement and trying different possibilities described in
http://www.mvps.org/access/general/gen0018.htm

but to no avail. Please help

 

[Granny Spitz via AccessMonster.com]

It seems failry straightforward
command but when it involves combination of criteria, simply fails.

It's really tricky and I don't know of a tutorial to guide you. You have to
code the criteria as a string and as if it were the where clause in a query,
but without the word WHERE. In your case the AND needs to be concatenated
with the other criteria like this:

Me.Serious = DCount("[Subject code]", "tblAdvEv", str1 & " And " & str2)

 

[Guest]

Hi mrazanaqvee,

The word "str" is a reserved word, used to invoke the Str function. This
function is used to return a variant (string) representation of a number. You
cannot set it equal to an expression. I recommend picking a different name
for your string variable. I'd also like to strongly encourage you to not use
spaces or special characters (such as the question mark) in anything that you
assign a name to in Access. Here are some good references for you:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Try downloading Allen's DB Issue Checker Utility and running it against your
database.

Are you using Option Explicit as the second line of code at the top of your
form's code module? If not, add these two very important words. Here is a gem
tip that discusses why this is important, and how to configure your VBA
editor so that you will get this added to all new modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Have you specifically dimensioned str1 and str2 as strings? If not, you
should. Here is a revised procedure that I believe you will find helpful.
Note how I changed:

str1 And str2
to
str1 & " And " & str2



Option Compare Database
Option Explicit

Private Sub cmdGetCounts_Click()
On Error GoTo ProcError

Dim str1 As String
Dim str2 As String
Dim strResult As String

'Update AE count
str1 = "[Subject code]='" & Me.Subject_code & "'"
strResult = DCount("[Subject code]", "tblAdvEv", str1)
Me.SECount = strResult

'Update SAE count
str2 = "[Serious Adverse Events?]=-1"
strResult = DCount("[Subject code]", "tblAdvEv", str1 & " And " & str2)
Me.Serious = strResult


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdGetCounts_Click..."
Resume ExitProc
End Sub




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi
I would really appreciate if someone could direct me towards a good
reference on DLookup/DCount usage and syntax. It seems failry straightforward
command but when it involves combination of criteria, simply fails. The
following is an example where I am stuck and would really appreciate input
from you guys.

I have a table named "tblAdvEv" in which are recorded adverse events for
every "Subject code" which may or may not be serious. This is recorded with a
Yes/No tickbox. On a subform "sfmVisitstatus" (on the parent form [Main
details]) there is a field which is updated with the On Current event of the
subform. I am trying get two pieces of info from the tblAdvEv table.

1. How many Adv events were encountered for each Subject code
2. How many Serious Adverse Events were encountered for each Subject code

The code which achieves counting (1) works perfectly
'Update AE count
str1 = "[Subject code]='" & Me.Subject_code & "'"
str = DCount("[Subject code]", "tblAdvEv", str1)
Me.SECount = str

But when I try to combine two criteria Subject code and [Serious Adverse
Event?]=True, it fails giving a type mismatch error:

'Update SAE count
str1 = "[Subject code]='" & Me.Subject_code & "'"
str2 = "[Serious Adverse Events?]=-1"
str = DCount("[Subject code]", "tblAdvEv", str1 And str2)
Me.Serious = str

I have tried different things: using criteria directly into the DCount
statement and trying different possibilities described in
http://www.mvps.org/access/general/gen0018.htm

but to no avail. Please help

 

[Guest]

Hi Granny,

Please contact me off-line, by private e-mail. My e-mail address is
available at the bottom of the contributors page indicated in my signature.


Thanks,

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Granny Spitz via AccessMonster.com]

Please contact me off-line, by private e-mail.

I'm sorry, Tom but I can't. My husband and I have an agreement. I don't do
things that make him jealous and he doesn't do things that make me jealous.
A friend has nagged me for some time to join the discussions in the
newsgroups to improve my skills, and I always declined because of my husband,
but we finally talked it over. One of the conditions of my participation is
that I don't collect male penpals. If you want to contact me privately
you'll have to go through my husband. He's out of town on business till
Tuesday but he'll contact you Wednesday night after work if you want. Let me
know and I'll find your address and give it to him.

 

[Guest]

Granny,

I'm not seeking to be a penpal, per say.
Yes, please have your husband initiate contact when he is available.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Dear Granny and Tom
Thanks both of you. It worked!

And many thanks for the links you posted, these are really helpful.

 

[Guest]

Hi Hi mrazanaqvee,

You're very welcome. Glad to hear that you got it working.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Granny Spitz via AccessMonster.com]

Thanks both of you. It worked!

You're welcome hon. I'm pleased it's working for you now.

 

[Granny Spitz via AccessMonster.com]

Yes, please have your husband initiate contact when he is available.

My husband contacted you so you should have received his messages by now.
Please don't share the confidential information with anybody else. Thank you.

 

[Guest]

Yes, I have received his message, and replied to it. Based on a reply I
received this morning, I believe he has shared my reply with you. (I'm not
sure why you are following up in this public forum to let me know, since it
should be obvious that we have been in contact).

Of course I will respect your request for confidentiality. I ask for the
same consideration from you and your husband, to never share anything in
public that I have written in a reply.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Granny Spitz via AccessMonster.com]

(I'm not
sure why you are following up in this public forum to let me know

I ask for the
same consideration from you and your husband, to never share anything in
public that I have written in a reply.

Our lips are zipped.