DCount syntax

[mrazanaqvee]

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

 

[Tom Wickerath]

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

 

[Tom Wickerath]

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

 

[Tom Wickerath]

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
__________________________________________

 

[mrazanaqvee]

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

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

 

[Tom Wickerath]

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
__________________________________________

 

[Tom Wickerath]

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
__________________________________________