#error

[dmyoungsal]

I am updating a form and have embeded the following into an unbound field, but when opening the form, the field displays "#Error"



=IIf([chkSDel]="-1",[tbxSlsRate],IIf([chkTMgrDel]="-1",[tbxSlsRate],IIf([from]="Stockton",[tbxStckRate],IIf([from]="Yuma",[tbxYumaRate],[tbxTruckRate]))))

The fields causing the problem are: [tbxSlsRate]; [tbxStckRate]; [tbxYumaRate]; [tbxTruckRate]

These fields are from 4 separate Dlookups in other fields looking at a query (i.e. =DLookUp("[TRKRate]","qryTruckRate")) which display the numbers contained in the fields in the query. In the case of the Dlookup, the field is named "tbxTruckRate"

Here is the contents of the original field (I am converting from embedded numbers to using a table that I can change the rates on the fly:
=IIf([chkSDel]="-1","75.00",IIf([chkTMgrDel]="-1","75.00",IIf([from]="Stockton","135.00",IIf([from]="Yuma","145.00","145.00"))))

any suggestions will be helpful.

[dmyoungsal]

the form using the lower "iif" statement has been in use for about 4 years.

[CJ_London]

why are you using quotations around all your values?

chkSDel and chkTMgrDel implies a check box which is a yes/no value with is numeric, not text
all the other fields appear to return numeric values, but you treat as text

please clarify the datatype for each value and what those values are - if you have any nulls, that will be one reason

[dmyoungsal]

why are you using quotations around all your values?

chkSDel and chkTMgrDel implies a check box which is a yes/no value with is numeric, not text
all the other fields appear to return numeric values, but you treat as text

please clarify the datatype for each value and what those values are - if you have any nulls, that will be one reason

Thanks for that input.... since I wrote this form some time ago, I do not recall why i put quotes around it, but removing them did not cause any problems.

When I open the form, the field I am having a problem with shows correctly (default of "140.00" - which comes from another field in the form ("txtTruckRate" = DLOOKUP("[TrkRate]","qryTruckRate")

but when i change the contents of the "from" field (which controls the contents of TruckRate field), the #error appears

[CJ_London]

just because the value is a number does not mean the field type is numeric, it may be a text field presenting a number. suggest substitute your dlookup in your formula

=IIf([chkSDel]="-1",[tbxSlsRate],IIf([chkTMgrDel]="-1",dlookup(....),IIf([from]="Stockton",dlookup(....),IIf([from]="Yuma",dlookup(....),DLOOKUP("[TrkRate]","qryTruckRate")))))

or better still, include your qryTruckRate in your recordset and calculate it there

[dmyoungsal]

after responding to your last comment (again, thanks), i had the exact thought about incorporating the "dlookup" into the areas that I was feeding the "txt" variables into. I literally just tested it and it worked flawlessly. Thanks for the inspiration.

[dmyoungsal]

CJ_London...... thank you!

[dmyoungsal]

after responding to your last comment (again, thanks), i had the exact thought about incorporating the "dlookup" into the areas that I was feeding the "txt" variables into. I literally just tested it and it worked flawlessly. Thanks for the inspiration.

I have another form that I use for pulling up a previous quote (that is essentially the same form and uses the same fields) and it works "most" of the time. I can select (or a different location in the "from" field and the Truck Rate appears correctly, and the calculation using the TruckRate field works, but every once in a while, I get a "type" error. But if I reenter or select another location, the calculation works fine.

A head scratcher/

[Welshgasman]

That will likely be your data at fault?