|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 14th, 2007, 05:30 PM
posted to microsoft.public.access.reports
|
|||
|
|||
The COUNT code works, but boy is it uuuuggggglllyyyyy!
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. 
|
|
#2
May 14th, 2007, 07:39 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
The COUNT code works, but boy is it uuuuggggglllyyyyy!
Try something like
=(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In ("Final" ,"OH","Platform","Mech"),1,Null)) -- Good Luck BS"D "J. Keggerlord" wrote: 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.
|
|
#3
May 14th, 2007, 07:39 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
The COUNT code works, but boy is it uuuuggggglllyyyyy!
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". -- Duane Hookom Microsoft Access MVP "J. Keggerlord" wrote: 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.
|
|
#4
May 15th, 2007, 09:25 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
The COUNT code works, but boy is it uuuuggggglllyyyyy!
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? "Ofer Cohen" wrote: Try something like =(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In ("Final" ,"OH","Platform","Mech"),1,Null)) -- Good Luck BS"D "J. Keggerlord" wrote: 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.
|
|
#5
May 15th, 2007, 09:44 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
The COUNT code works, but boy is it uuuuggggglllyyyyy!
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 "J. Keggerlord" wrote: 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? "Ofer Cohen" wrote: Try something like =(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In ("Final" ,"OH","Platform","Mech"),1,Null)) -- Good Luck BS"D "J. Keggerlord" wrote: 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.
|
|
#6
May 15th, 2007, 10:03 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
The COUNT code works, but boy is it uuuuggggglllyyyyy!
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. "Ofer Cohen" wrote: 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 "J. Keggerlord" wrote: 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? "Ofer Cohen" wrote: Try something like =(Count(IIf(tblTools!Disposition="New Tool" And [tblTools!PrefixBasic] In ("Final" ,"OH","Platform","Mech"),1,Null)) -- Good Luck BS"D "J. Keggerlord" wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
