The COUNT code works, but boy is it uuuuggggglllyyyyy!

[J. Keggerlord]

Is there a "cleaner" way to create this code? I have a pretty heinous
looking DSUM code that is encountering the same problem (multi-criteria
screening before calculating value). I tried to group the screened values of
PrefixBasic using parenthesis before adding the 'And' criteria for
Disposition. Obviously, this confused the Iif and Count function. Anyone
who can make this a little neater gets a smiley emoticon and my thanks! (Hard
breaks inserted for code clarity)

=(Count(IIf([tblTools!PrefixBasic]="Final" And tblTools! Disposition="New
Tool" Or
[tblTools!PrefixBasic]="OH" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Platform" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Mech" And tblTools!Disposition="New Tool",1,Null)))

*** The purpose of the function is to find all of the "Final", "OH",
"Platform" and "Mech" tools that are also dispositioned as "New Tool" and
then count them.

 

[Ofer Cohen]

Try something like

=(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In
("Final" ,"OH","Platform","Mech"),1,Null))

 

[Duane Hookom]

You can try something like:
=Count(IIf(tblTools!PrefixBasic IN ("Final","OH","Platform","Mech") AND
tblTools!Disposition ="New Tool", 1, Null))

However, you should have a field value somewhere that identifies certain
PrefixBasic values as being a significant group. IMHO you should not be
hard-coding values like "Final","OH","Platform","Mech".

 

[J. Keggerlord]

I tried this, but no joy. Still, it's not as bad as my DSum function, which
reads like Tolstoy, but I figure if I can get one to work out, the other
might be made easier. This is more of an informational post since I've
resigned myself to accept the code as it is, for now.

In any case, I did find an interesting condition when performing a DSum
calculation, where if the criteria aren't met, it returns a "null" value. In
a statement where you are adding two DSum values and one comes back null,
your result is a null value. Needless to say, the only way I could think to
eradicate it was to make a statement along the lines of:
=(Iif (IsNull (<<DSUM STRING 1>>),0,<<DSUM STRING 1>>) +
(Iif (IsNull (<<DSUM STRING 2>>),0,<<DSUM STRING 2>>)

Again, not pretty, but pretty functional. Any other thoughts on
accomplishing the same task?

Try something like

=(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In
("Final" ,"OH","Platform","Mech"),1,Null))

--
Good Luck
BS"D

Is there a "cleaner" way to create this code? I have a pretty heinous
looking DSUM code that is encountering the same problem (multi-criteria
screening before calculating value). I tried to group the screened values of
PrefixBasic using parenthesis before adding the 'And' criteria for
Disposition. Obviously, this confused the Iif and Count function. Anyone
who can make this a little neater gets a smiley emoticon and my thanks! (Hard
breaks inserted for code clarity)

=(Count(IIf([tblTools!PrefixBasic]="Final" And tblTools! Disposition="New
Tool" Or
[tblTools!PrefixBasic]="OH" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Platform" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Mech" And tblTools!Disposition="New Tool",1,Null)))

*** The purpose of the function is to find all of the "Final", "OH",
"Platform" and "Mech" tools that are also dispositioned as "New Tool" and
then count them.

 

[Ofer Cohen]

In that case it better to use the Nz function to replace Null with Zero

=Nz(<<DSUM STRING 1>>,0) + Nz(<<DSUM STRING 2>>,0)

--
Good Luck
BS"D

I tried this, but no joy. Still, it's not as bad as my DSum function, which
reads like Tolstoy, but I figure if I can get one to work out, the other
might be made easier. This is more of an informational post since I've
resigned myself to accept the code as it is, for now.

In any case, I did find an interesting condition when performing a DSum
calculation, where if the criteria aren't met, it returns a "null" value. In
a statement where you are adding two DSum values and one comes back null,
your result is a null value. Needless to say, the only way I could think to
eradicate it was to make a statement along the lines of:
=(Iif (IsNull (<<DSUM STRING 1>>),0,<<DSUM STRING 1>>) +
(Iif (IsNull (<<DSUM STRING 2>>),0,<<DSUM STRING 2>>)

Again, not pretty, but pretty functional. Any other thoughts on
accomplishing the same task?

Try something like

=(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In
("Final" ,"OH","Platform","Mech"),1,Null))

--
Good Luck
BS"D

Is there a "cleaner" way to create this code? I have a pretty heinous
looking DSUM code that is encountering the same problem (multi-criteria
screening before calculating value). I tried to group the screened values of
PrefixBasic using parenthesis before adding the 'And' criteria for
Disposition. Obviously, this confused the Iif and Count function. Anyone
who can make this a little neater gets a smiley emoticon and my thanks! (Hard
breaks inserted for code clarity)

=(Count(IIf([tblTools!PrefixBasic]="Final" And tblTools! Disposition="New
Tool" Or
[tblTools!PrefixBasic]="OH" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Platform" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Mech" And tblTools!Disposition="New Tool",1,Null)))

*** The purpose of the function is to find all of the "Final", "OH",
"Platform" and "Mech" tools that are also dispositioned as "New Tool" and
then count them.

 

[J. Keggerlord]

Sweet! That is a slick function that I never would have found on my own!
Thank you, Mr. C! That actually cleans up the code by quite a bit.

In that case it better to use the Nz function to replace Null with Zero

=Nz(<<DSUM STRING 1>>,0) + Nz(<<DSUM STRING 2>>,0)

--
Good Luck
BS"D

I tried this, but no joy. Still, it's not as bad as my DSum function, which
reads like Tolstoy, but I figure if I can get one to work out, the other
might be made easier. This is more of an informational post since I've
resigned myself to accept the code as it is, for now.

In any case, I did find an interesting condition when performing a DSum
calculation, where if the criteria aren't met, it returns a "null" value. In
a statement where you are adding two DSum values and one comes back null,
your result is a null value. Needless to say, the only way I could think to
eradicate it was to make a statement along the lines of:
=(Iif (IsNull (<<DSUM STRING 1>>),0,<<DSUM STRING 1>>) +
(Iif (IsNull (<<DSUM STRING 2>>),0,<<DSUM STRING 2>>)

Again, not pretty, but pretty functional. Any other thoughts on
accomplishing the same task?

Try something like

=(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In
("Final" ,"OH","Platform","Mech"),1,Null))

--
Good Luck
BS"D


:

Is there a "cleaner" way to create this code? I have a pretty heinous
looking DSUM code that is encountering the same problem (multi-criteria
screening before calculating value). I tried to group the screened values of
PrefixBasic using parenthesis before adding the 'And' criteria for
Disposition. Obviously, this confused the Iif and Count function. Anyone
who can make this a little neater gets a smiley emoticon and my thanks! (Hard
breaks inserted for code clarity)

=(Count(IIf([tblTools!PrefixBasic]="Final" And tblTools! Disposition="New
Tool" Or
[tblTools!PrefixBasic]="OH" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Platform" And tblTools!Disposition="New Tool" Or
[tblTools!PrefixBasic]="Mech" And tblTools!Disposition="New Tool",1,Null)))

*** The purpose of the function is to find all of the "Final", "OH",
"Platform" and "Mech" tools that are also dispositioned as "New Tool" and
then count them.