Dcount problem

[Inkognito]

Hi!

I have been reading hundreds of threads in different forums to get an understanding of why I have this problem, but no luck.

I have a table of a lot of members in a boat-club, more specifically which members that have ordered help with their boat. And i need to count the total number of members in the entire table that have ordered help.

The table consists of different hours in the day, and which members are scheduled where. The hours that have no schedule, have the value "0".

So basically i need to count every post that is NOT 0 in several columns.

The table name is bestilling, and i need to count in the columns medlemsnr1 - 21.

I am using Dcount like this:

Tekst736 = DCount("[Medlemsnr2]", "bestilling", Medlemsnr2 = not "0")

But this results in "0" in the output textbox.

if i change to:
Tekst736 = DCount("[Medlemsnr2]", "bestilling", Medlemsnr2 = "0")

it outputs 151, which is the total number of rows in the table. So it kindoff believes that every post is 0, which is not the case. About half of the posts are 0.

Any ideas?

 

[jal]

Not sure, but for starters I would think the third item needs to be wrapped in quotes


Tekst736 = DCount("[Medlemsnr2]", "bestilling", Medlemsnr2 = "0")

Maybe this:


Tekst736 = DCount("[Medlemsnr2]", "bestilling", "Medlemsnr2 = 0")

 

[Inkognito]

Great I think we might be on to something. But now i get "Data type mismatch in criteria expression". Which is an error i have been getting a lot lately.

 

[Inkognito]

As i am rewriting my table to having "" instead of "0" as the default value for no schedule, it all seems to be working with "Not isnull([Medlemsnr2]".

So then i guess I'll just be doing some rewriting then

 

[Inkognito]

Omg, sorry for triple post. Seems that it wont really be possible to change this, 'cause of relations and such, so I if anyone still have a hint, I'd be thrilled

 

[WayneRyan]

Ink,

If you want a count of the non-zero values AND the null values:

Tekst736 = DCount("[Medlemsnr2]", "bestilling", "Nz([Medlemsnr2]) <> 0")

Wayne

 

[Inkognito]

I just wanted to count all the values that were not "0". But I found a way to use "" instead of "0" now using "0" just made more and more problems

Thx though, but I'll use the Not isnull([Medlemsnr]) function it works perfectly

 

[Inkognito]

I am back to say that i had to use "0" as the empty value anyway, as the database seemed to be skipping posts when i used nothing.

And your way worked perfectly, Wayne! But i got "Data type mismatch in criteria expression" when i tried to work with "0", but as i replaced it with chr(48) the problem was solved!