Running Average Quote based on Part number (1 Viewer)

[maacky99]

I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.

On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?

If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.

 

[RichO]

You would have to use VBA and DAvg to get the figure for the current model #. Using Nz([Price])>0 will ignore zeros and nulls.

DAvg("[Price]","Repairs","[Model#]='" & Me.Model# & "' And Nz([Price]) > 0")

 

[maacky99]

I gave that a try and it didn't work. I get the #name? error.

I'm looking at the code and now that I see how you went about it I understand your thinking and it looks good. Now I'm really stumped.

 

[RichO]

First you need to find out if DAvg is returning a null value. Change the line to this...

Nz(DAvg("[Price]","Repairs","[Model#]='" & Me.Model# & "' And Nz([Price]) > 0"), 0)

If your textbox displays 0 then it means that DAvg is returning a null. To begin with are you assigning this expression to your textbox in VBA? If so, how are you coding it?

If it is returning a null, check your field names over and make sure they match the names in the code exactly. Also, is [Model#] a numeric field or text? Currently the code is looking for a text field because the example you showed in your original post had quotations around the number. If it is a numeric field you will want to remove the ' and '.