DCount giving error for all records

[djsmith2005]

I am trying to use DCount to count the number of records (speakers) for each session. Not being very savvy with DCount, I copied code that I had working for another instance of needing to count the number of records.

My query has three fields
Query used: Web/PrintReport_qry
Session Id - number
Speaker - string

SpeakerCount: DCount("*","Web/PrintReport_qry","[Session ID]='" & [Session ID] & "' And [Speaker]<" & [Speaker])+1

My output for SpeakerCount is "#Error"

Your help is appreciated as I need to get this series of reports completed this morning.

 

[pr2-eugin]

Why is that you are using Domain function inside a Query? This will make the Query very slow and inefficient.. Try TOTALS query instead.

 

[djsmith2005]

Lame excuse, but this is what I have learned so far.

 

[namliam]

is session id by chance a number field?
Is Speaker a number field?

Is the +1 intended to be outside of the DCount?

 

[djsmith2005]

session id is a number
Speaker is a string

Yes, that is what I was shown in the previous instance of needing to count the number of records associated with one criteria. What it did was add a number to the DCount field for each record associated with an organization, I was then able to extrapolate each record to include in a mail merge that went to the organization.

 

[namliam]

You need to handle numbers, strings and dates differently in these kind of things.
Text: "[Session ID]='" & [Session ID] & "'"
Date: "[Session ID]=#" & [Session ID] & "#"
Number: "[Session ID]=" & [Session ID] & ""

Because you are lacking the ' around speaker you probably get the error

 

[djsmith2005]

I tried to add the ' around speaker but that did not change anything.

SpeakerCount: DCount("*","Web/PrintReport_qry","[Session ID]='" & [Session ID] & "' And '[speaker]'<" & '[Speaker]')+1

I do have an option to add in the speaker's contact number, but when I tried using the Contact number it made no difference in my DCount field.

Thank you for getting back to me and assisting me, I appreciate your time.

 

[namliam]

Erm, the way you did that is not exactly right and since Session ID is a number field, you also have to remove the ' around that

SpeakerCount: DCount("*","Web/PrintReport_qry","[Session ID]=" & [Session ID] & " And [speaker]<'" & [Speaker] & "'")+1

using < on a text field though, that can be tricky... but its possible.

 

[djsmith2005]

Thank you that worked perfect.

Do I understand that the Session ID = Session Id AND Speaker (Concatenation) Is less than Speaker , add 1 to the SpeakerCount? the trick as you stated before is that text, dates and numbers are handled differently.

Thank you again for your assistance.

 

[namliam]

The basic premise is this, Dcount() + 1
So the DCount returns something for the count you want to do, for example 10 and you do +1 returning 11.
Or if Dcount returns 0, then + 1 = 1

So DCount will always return its own count and always add 1 for each record it will find, regardless of the +1 behind it

 

[djsmith2005]

Thank you for the explanation as well. You have helped me in getting my reports completed this morning.

Have a terrific day.