Report Sorting/Grouping statement too long to edit

[Chuck]

The too long to edit Statement is:

=IIf([Rcd]<=36,(2*[Rcd]+5-((([Rcd]-1) Mod 12))), _
IIf([Rcd]>36,IIf(Int((([Rcd]-((([Rcd]-1) Mod _ 6)+1))/6)/2)= _
(([Rcd]-((([Rcd]-1) Mod 6)+1))/6)/2,(-2*[Rcd]+150)+ _
(((([Rcd]-37) Mod 6)*3)-9),((-2*[Rcd])+150)+ _
(((([Rcd]-37) Mod 6)*3)-15)),0)),"")

The statement can be broken into two parts at the second IIf. But then I don't
know how to take the output of the first statement and feed it into the second
statement.

Any suggestions will be appreciated.

Chuck

 

[Duane Hookom]

This looks like a "business rule" type expression. Create a small user
function that accepts the Rcd field and returns an appropriate value.

There is no way that I would attempt to create an expression this complex.
Another issue you have is the IIf() might return a number or might return a
string. IMHO this is never a good idea. Perhaps change the final ,"") to ,
Null).

 

[Chuck]

This looks like a "business rule" type expression. Create a small user
function that accepts the Rcd field and returns an appropriate value.

There is no way that I would attempt to create an expression this complex.
Another issue you have is the IIf() might return a number or might return a
string. IMHO this is never a good idea. Perhaps change the final ,"") to ,
Null).

Thanks for the reply Duane.

The first IIf statement is only a filter. So it has been moved to the report's
underlying query. The rest of the statement is still in the report's sorting
and grouping because that is exactly what it does.

The first IIf statement returns an integer number, however the final ,"") has
been changed to Null).

So far it seems to be working correctly, but a lot more checking is required to
be certain.

Chuck
--

 

[Duane Hookom]

IMHO, a small user-defined function would be much easier to create, maintain,
and test.

 

[Chuck]

IMHO, a small user-defined function would be much easier to create, maintain,
and test.

I believe you. But I wasn't up to the task. I do most if my equation
development in Excel where I can make plots of the results to check what I'm
doing. The syntax for And, Or, Mod, and probably a few others as well,
statements are slightly different in Access, but the translation is quite easy.
I've run enough checks now to see that it code produces the desired results.

Is it possible to just take the sorting code and put in tact it into a simple
private function in the report module? Give the function a name something like
SrtOrder then Put SrtOrder in the Sorting/Grouping box?

Chuck
--

 

[Duane Hookom]

You could create a function in Access or Excel. In either application, you
would create a new module and enter code like this (I'm not going to try to
figure it out). In Access you would then save the module as
"modBusinessCalcs".
Public Function GetSort(dblRcd as Double) as Double
GetSort=IIf(dblRcd<=36,(2*dblRcd+5-(((dblRcd-1) Mod 12))), _
IIf(dblRcd>36,IIf(Int(((dblRcd-(((dblRcd-1) Mod _ 6)+1))/6)/2)= _
((dblRcd-(((dblRcd-1) Mod 6)+1))/6)/2,(-2*dblRcd+150)+ _
((((dblRcd-37) Mod 6)*3)-9),((-2*dblRcd)+150)+ _
((((dblRcd-37) Mod 6)*3)-15)),0)),Null)
End Function

In either Access or Excel you could then call the function with a reference
to the field or a cell
=GetSort([Rcd])
=GetSort(D7)

 

[Chuck]

You could create a function in Access or Excel. In either application, you
would create a new module and enter code like this (I'm not going to try to
figure it out). In Access you would then save the module as
"modBusinessCalcs".
Public Function GetSort(dblRcd as Double) as Double
GetSort=IIf(dblRcd<=36,(2*dblRcd+5-(((dblRcd-1) Mod 12))), _
IIf(dblRcd>36,IIf(Int(((dblRcd-(((dblRcd-1) Mod _ 6)+1))/6)/2)= _
((dblRcd-(((dblRcd-1) Mod 6)+1))/6)/2,(-2*dblRcd+150)+ _
((((dblRcd-37) Mod 6)*3)-9),((-2*dblRcd)+150)+ _
((((dblRcd-37) Mod 6)*3)-15)),0)),Null)
End Function

In either Access or Excel you could then call the function with a reference
to the field or a cell
=GetSort([Rcd])
=GetSort(D7)

Thank you. I think that will be enough to get me going.

Chuck
--

 

[Chuck]

You could create a function in Access or Excel. In either application, you
would create a new module and enter code like this (I'm not going to try to
figure it out). In Access you would then save the module as
"modBusinessCalcs".
Public Function GetSort(dblRcd as Double) as Double
GetSort=IIf(dblRcd<=36,(2*dblRcd+5-(((dblRcd-1) Mod 12))), _
IIf(dblRcd>36,IIf(Int(((dblRcd-(((dblRcd-1) Mod _ 6)+1))/6)/2)= _
((dblRcd-(((dblRcd-1) Mod 6)+1))/6)/2,(-2*dblRcd+150)+ _
((((dblRcd-37) Mod 6)*3)-9),((-2*dblRcd)+150)+ _
((((dblRcd-37) Mod 6)*3)-15)),0)),Null)
End Function

In either Access or Excel you could then call the function with a reference
to the field or a cell
=GetSort([Rcd])
=GetSort(D7)

After much consternation the program is working. However, I'm not nearly so
cleaver as I thought I was. In translating Excel code to Access code I made
several errors. Those errors were nor readily obvious because the translated
codes compiled properly and ran clean. They just gave wrong answers. The
problem was that the translating errors resulted in logic errors in Access. It
took a several attempts to catch them all.

Chuck
--