#Name error in my form

[jlindquist23]

I have a query with a formula:
BendTestsReq: IIf(Val([SageBTLot])=0,"",(IIf(Val([JO_Qty])<Val([SageBTMin]),Val([SageBTMin]),(Val([JO_Qty])/Val([SageBTLot]))*Val([SageBTQty]))))

When I run the query, it shows the correct value:
When I add the field to my form, it shows this:



Not sure what is happening here. on the form it is setup as a general number with zero decimal points.
Thanks!
Jennifer

[June7]

For some reason, Access can't find an object.

If you want to provide db for analysis, follow instructions at bottom of my post.

[rpeare]

you are treating a single field as both text and a number for one thing. I doubt that's the main problem here but it doesn't help


BendTestsReq: IIf(Val([SageBTLot])=0,0,(IIf(Val([JO_Qty])<Val([SageBTMin]),Val([SageBTMin]),(Val([JO_Qty])/Val([SageBTLot]))*Val([SageBTQty]))))

BendTestsReq: IIf(Val([SageBTLot])=0,Null,(IIf(Val([JO_Qty])<Val([SageBTMin]),Val([SageBTMin]),(Val([JO_Qty])/Val([SageBTLot]))*Val([SageBTQty]))))

either of these will work better

use NULL if you don't want any value at all to appear, use 0 otherwise.

If you try to sort your query after you run it (click a column header and choose a to z or z to a) I suspect you'll get an error. this usually happens when you've got a formula in your query Access can't interpret. For instance doing a date difference (DATEDIFF) calculation when one of the dates is missing, or adding a text value to a number field etc. If you do get an error you'll have to take out your calculated fields one at a time until you get the query to run, then figure out where the error is if it doesn't leap out at you.

[GinaWhipp]

Hmm, that will sometimes happen if the Control name on your Form is the same name as the Field name in your Query OR if your Control Name shares the name of one of the Fields in your calculation. Try giving the Control another name and see if that corrects the problem.

[rpeare]

Excellent point Gina, hadn't thought about that

[jlindquist23]

Hmm, that will sometimes happen if the Control name on your Form is the same name as the Field name in your Query OR if your Control Name shares the name of one of the Fields in your calculation. Try giving the Control another name and see if that corrects the problem.

I closed the database and reopened it and it worked. I ... can't... even...

[GinaWhipp]

Too funny but it does happen... go have some coffee!