DCount not working

[Hayley Baxter]

I am using the DCount function to count the values of my category's field. I have used this many times before and I can't see any problems with my syntax but yet I keep getting #error returned I'm puzzled can anyone see what I can't?

=DCount("[CategoryID]","tblProspects","[CategoryID] = 'Newscorp'")

Many thanks
Hay

 

[skiphooper]

Hi Hayley,

I'm probably all wrong, but

=DCount("[CategoryID]","tblProspects","[CategoryID] = " & "Newscorp")


Skip
_____

 

[Hayley Baxter]

Skip

You deserve a gold star that's what it was.

Thanks a bunch
Have a great weekend!
Hay

 

[Hayley Baxter]

Just double checking and this returns all records not what I need an the = to is what I need not &. I checked it against the help files and on another database that I have previously used it on and the syntax is exact with the exception of the field and table names..oh why does it always rain on me as Travis says and on Fridays as well!!

Any ideas for this strange behaviour??

 

[RichMorrison]

Hayley,

I think it is this
<<
"[CategoryID] = 'Newscorp'"
>>
in the original expression.

The string is not parsed correctly because of the mix of single and double quotes. Access does have restrictions for the use of quotes withing a string expression.

The form:
"[CategoryID] = " & "'" & "Newscorp" & "'"
is more reliable.

RichM

 

[Hayley Baxter]

Rich

Here is the strange part I created a new table put in a field called test and entered test1 as the data. I put in the above syntax to match and hey the counts works but not with my table it needs to work on.

I've got the rowsource for this looking up another table could this stop it from working? I don't see how the bound column is my idno and the category name field is what I need to count.

I think this is causing the problem as the data type is set to number and it is joined to tblCategory.

Very frustrated

 

[Hayley Baxter]

Yes I have finally got it only took me all afternoon.

Pat Hartman I'm so glad your posts are on this forum you're a genius..cheers for yet another answer!

For anyone who's curious pat gives good advice of removing the single quotations when it's a number field

Let's hear it for Pat!
Thanks
Hay

 

[skiphooper]

Hayley,

So what was the solution ?

Skip
_____

 

[Hayley Baxter]

Well because I am looking up the values in another table my bound column is the id number and because it is a number ie newscorp is category 5 in my table

You have to remove the single quotations marks at either side of the 5 if you look to my last syntax there is 'newscorp'"" you cannot use that on a number field.

Here is what I ended up with

=DCount("[CategoryID]","tblProspects","[CategoryID] = 5")

Hay

 

[RichMorrison]

You wrote

<<
I think this is causing the problem as the data type is set to number and it is joined to tblCategory.
>>

Gee !!!!!
Could that be it ???????????????



RichM