Problem with DCount Statement

[M_S_Jones]

I'm trying to return the number of records in my table that have a Department_ID of 4. However, the statement returns the total number of records in the table, whatever the value of their Department_IDs. Here's the statement:

= DCount("[Department_ID]", "tabDepartment_Rates", "[Department_ID] = 4")

Do any of you have any ideas as to what the problem is?

Regards,

Matt

 

[KenHigg]

If [Department_ID] is a text field you may have to put the value in quotes:

DCount("[Department_ID]", "tabDepartment_Rates", "[Department_ID] = '4'")

???

 

[M_S_Jones]

Affraid not, it's an autonumber. If I completely remove the criteria section from my statement, it still returns the same results, it seems to be ignoring it. Any other ideas?

 

[KenHigg]

Where are you placing this code?

 

[M_S_Jones]

In the code for the OnClick event on a button. I'm storing the result in a variable which I am then returning in a MsgBox on click of the button. I was planning on using the result in an if statement, but wanted to check that it was returning correct values before I implemented it; and it's not. What's your trail of thought?

 

[KenHigg]

None really - Looks like you have the code correct - We must be missing something really simple. Have you tried doing a query and use 4 as a criteria there? Then do a totals query and see what happens...

 

[M_S_Jones]

Using a totals query I've managed to get exactly what I wanted, so I may just use that instead. Thanks for the suggestion and your time.

Matt

 

[KenHigg]

Maybe if you do a dcount("*",etc ??

 

[M_S_Jones]

No, that didn't work either. I'm happy to just use the query, it worked first time.

 

[KenHigg]

Cool...

(But not knowing why the other didn't work would drive me crazy... )

 

[M_S_Jones]

It's Friday afternoon, I'll just be happy to get this form finished; doesn't matter how Thanks again.

 

[KenHigg]

You're welcome - Have a good weekend