text to long to calculate in Query

[DJKAOS]

I'm working on a querry but it shows a calclation from some data in the table the problem is the calculation is to long and complex to fit in the query field...so it wont let me do it. anyone got any ideas how I can fix this? Thanks.

 

[MichaelRed]

place the calculation in a standard module (Public procedure) and set the results to the calculation, with the arg, list.

a BRIEF (and uninteresting) example:

Query thing

Foo: FooBar(Foo, Bar)

Module Stuff

Public Function FooBar(Foo as Integer, Bar as Integer) As double

[tab]FooBar = (Foo * Bar)

End Function



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[DJKAOS]

Thanks, that helped. But I'm getting weird errors for some reason. on some records.

here is my function.

Public Function AccPassing_6(DryMass as Double, Mass_6 as Double) As Variant
if(DryMass <> Null)then
AccPassing_6 = (100-((1/DryMass) * Mass_6 * 100))
else
AccPassing_6 = Null
endif
end function

But on a few records I get #error as the answer and I dont see why.

I tried putting an IIf statment in the query field..like iif(bla > 0, call my function, null)
that kinda worked..but why doesn't the code in my function seem to work for every record?

Thanks for any help

 

[tlbroadbent]

Don't explicit declare a data type for the function parameters (DryMass and Mss_6). Allow them to be variants. Then if the column in the table is Null the function call won't fail as it does not when the params are declared as Double.

I would also make some other minor modifications to the function.

Public Function AccPassing_6(DryMass, Mass_6) As Variant

If Isnumeric(DryMass) And IsNumeric(Mass_6) Then
AccPassing_6 = 100-(Mass_6 / DryMass * 100)
Else
AccPassing_6 = Null
EndIf

end function
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[DJKAOS]

Thanks a lot, I got it working now I figured out the variant part a little after I posted my message. I didn't use isnumeric..but what I did works I think.

If(Nz(DryMass+0)>0 And Nz(Mass_6+0)>0)then
bla bla
else
bla bla
endif


Thanks again