Help getting a formula that works in Forms but not in a Query

[PHILnTEX]

Hi All,

I been trying to figure out how to get a formula that works in Excel and Access Forms to work in an Access Query.
I’ve attached a graphic showing what I’m able to do in Excel and also attached the sample access Db that shows it working in a Form.

In short, I looking to get the average weight (+/- 15%) per item within a specific class. Sounds simple doesn’t it….. But I’ve been working on this for days with no success.

These are the working formulas:
Excel(see pic): =AVERAGEIFS($C$3:$C$26,$A$3:$A$26,"="&A7,$C$3:$C$26,">="&C7-(C7*$D$1),$C$3:$C$26,"<="&C7+(C7*$D$1))
Access Form: =DAvg("[Weight]","[Table1]","[Class] = [txtClass] AND [Weight]>=([txtWeight]*(1-.15)) AND [Weight]<=([txtWeight]*(1+.15))")

Your help and thoughts would be greatly appreciated!

 

[moke123]

what about a UDF and use that in your query.

 

[PHILnTEX]

Thanks for your suggestion moke123.
I haven't written a function like this before. It might be a little over my head but I'll look into that.
Thanks again

 

[JHB]

Put the below in:

wAvg: DAvg("[Weight]";"[Table1]";"[Class] ='" & [Class] & "' AND [Weight]>=(" & Replace([Weight];",";".") & "*(1-.15)) AND [Weight]<=(" & Replace([Weight];",";".") & "*(1+.15))")

Because we in our language use comma as at decimal place, I need to replace it.
Maybe you don't need it.

 

[PHILnTEX]

Fantastic!!!! Yes that did the trick.

I just need to replace ; with , and worked just as you wrote it!

Thanks so much JHB!!! You really made my day

 

[JHB]

You're welcome, good luck.
The ; and the , is another language special because we use , at decimal place.