Runtime Error 6: Overflow problem

[cms2012]

Hey everyone,

I know this problem has come up on the forums and I have read the threads to see if they will help me solve my problem but I cannot get the problem fixed. I am trying to get a profit margin percentage by doing a Davg of the two fields I need from a query I have. Here is the code:

Me.txtmargin = DAvg("Profit / TOTAL_QUOTE", "MARGIN", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdyear)

I keep getting an overflow run-time error. I have tried converting both fields into doubles and long integers but it is still giving me the error. If someone could help me out I would be greatly appreciative. Thank you!

 

[MarkK]

Is txtMargin a bound field? Is it of the proper type to receive the data you are assigning it?

If code breaks on the line when you get the error then you can troubleshoot as follows... Open the immediate window and run parts of the DAvg() until you isolate what causes the error. So try...

? DAvg("Profit / TOTAL_QUOTE", "MARGIN")

Does that fail? Now keep adding bits...

? DAvg("Profit / TOTAL_QUOTE", "MARGIN", "metMONTH = " & holdmonth)

Does that fail? Keep doing that till you isolate the failure.

Hope that helps,
Mark

 

[cms2012]

It is an unbound text box and I'm pretty sure it is the proper type to accept the data , and the code is failing at the

? DAvg("Profit / TOTAL_QUOTE", "MARGIN")

 

[pr2-eugin]

Try enclosing them in Square brackets, as you have used a special character inside your field name.. change your code to

? DAvg("[Profit / TOTAL_QUOTE]", "[MARGIN]")

Try it out, I think that should sort you out.

 

[MarkK]

pr2, I think those are two fields, so it would be...

DAvg("[Profit] / [TOTAL_QUOTE]", "[MARGIN]")

Check your data, this DAvg() is calculating on every row of your table in this case, so if there's a zero in Total_Quote, that division fails, and then what happens to the DAvg()???
We're narrowing it down anyway.

 

[cms2012]

I enclosed them in brackets and got:

Run-time error '2741':
The expression you entered as a query parameter produced this error: '[Profit/TOTAL_QUOTE]'

 

[pr2-eugin]

@ Iagbolt - Oh I see, was not sure they were two separate fields. My bad..
@ cms2012 - Try Iagbolt's suggestion. If they are two separate fields you have to enclose them in square brackets. Also make sure they are not ZERO.

 

[cms2012]

I think the problem is that there is zeros in the TOTAL_QUOTE, I didn't know there were. Would this cause the overflow?

 

[MarkK]

To test if they cause the overflow, remove them and see if it works...

? DAvg("Profit / TOTAL_QUOTE", "MARGIN", "Total_Quote <> 0")

Does it work?

 

[cms2012]

It works if I do that! The only thing is that I have to ask my boss if he wants me to leave them out lol. Thanks for the help everybody!!