DCount syntax

[Peter Bellamy]

I am trying to simplify a DCount statement as it has to make 10 tests in its criteria.

The a slightly simplified version for just 2 criteria is:
crit1 = "SPMB2/001"
crit2 = "SPCOH2/001"

number = DCount("[goods_no]", "Goods", "[goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit1 & "' Or [goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit2 & "'")

So what I would like to do is contruct 10 lines like:
crit01 = "[goods_returnno]=Form.[return_no] And [goods_partno]= 'SPMB2/001'"
crit02 = "[goods_returnno]=Form.[return_no] And [goods_partno] = 'SPCOH2/001'"
and concatanate them with Or's in between.

I don't seem to be able to get the correct syntax for crit01 and crit02.
Can someone point me in the right direction?

Cheers

 

[namliam]

You need to work with () to enforce the right interpertation of the OR

(.. and ...) or (... and .. )

 

[Peter Bellamy]

Thanks, I have tried that and now I can't get the Dcount syntax right!
Can you help me again?

pnb

 

[gemma-the-husky]

the easiest way to do stuff like this, when its hard to get the SQL right, is to design it as a visual query

then you can just do

dcount("*","myvisualquery")

 

[Peter Bellamy]

Good tip, it now works fine !!

However I must get to grips with all those "s, 's and &s, if only for the mental challenge!

Thanks again

pnb

 

[namliam]

DCounts, Dmax etc should be used at a minimum, A MINIMUM

They on average are NOT the best solution, they have their place but use them SPARINGLY

That said

number = DCount("[goods_no]", "Goods", " ( [goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit1 & "' ) Or ( [goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit2 & "' )")

Should do the trick... or
Part1 = " ( [goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit1 & "' ) "
Part2 = " ( [goods_returnno]=Form.[return_no] And [goods_partno]= '" & crit2 & "' ) "
number = DCount("[goods_no]", Part1 & " Or " & Part2)

Would make it slightly more readable....

JUST REMEMBER, use D...anything with great care and at your own perrel!!!!

 

[Peter Bellamy]

Thanks namliam, I know they can give unexpected results.
Dave's suggestion worked jus fine.

Thanks all

 

[Peter Bellamy]

For this example, what would be another way, not using DCount, to determine how many records there were in a opened table that matched a number of criteria?

pnb

 

[namliam]

For this example, what would be another way, not using DCount

Use a normal select query to retrieve the data

 

[Peter Bellamy]

There is a SELECT count available using SQl I believe.
What is the syntax for this to return a value to my code?

Thanks

 

[namliam]

read up one the "openrecordset" command look at the samples if need be search the forum on that... Should be able to find it...