Link to home
Start Free TrialLog in
Avatar of VBAQuestions
VBAQuestions

asked on

Access Calculated Field Error in Form

Hi, I have a calculated field in a form that receives user input.  The calculated field works as it should, but until the data that it needs to make its calculation is entered the field displays a #Type! error.  Does anyone know how I can avoid that displaying in the field box?  I don't want the user to think there is a problem with the form.  I have attached a picture to show the error in the form.  Thanks so much for your help!
ExForm.PNG
Avatar of als315
als315
Flag of Russian Federation image

You shoul check Null values in calculated fields. Something like this:
iif(isnull([MyField],"",[MyField]*5)
Is the calculation being done in the form or in the the underlying RecordSource for the form.

As a general rule, I like to do my calculations n the recordsource, so I can run the query alone and see if the results are correct before making a form or report.,

The other advantage to this approach is that you now only need this one query to create a form or report (an not have to recreate this calculation over for each object)
Avatar of VBAQuestions
VBAQuestions

ASKER

In the table that holds the operator information I created a calculated field that does the addition automatically for each record.
...OK so then you are probably using Access 2010...
I'm not a big fan on calculations in a table (because these "Access only Fields" do not carry over to SQL Server very well)
I prefer to make a query (where I can join other tables if needed)

But you are still on the right track...
At least you don't have to recreate the calculation for each Form/report.
That is the whole reason for my post.

So it seems like  als315 solution should be your answer

;-)
Can you show us the expression? Without it, we can only make very broad generic suggestions...
(°v°)
The expression just adds five years to the date given and works fine.  The problem is that until the date is entered the calculated field shows the error in the picture attached to the original post on the form.
Have you tried als315's suggestion?  Your calculated field should be something like
IIF(IsNull ([NameOfDateField]), "", DateAdd ("y", 5, [NameOfDateField]))

Open in new window

With that I get an error that the expression cannot be used in a calculated column.
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<With that I get an error that the expression cannot be used in a calculated column. >
You are still not posting the expression you are using...
Thanks for everyone's input!  It displays perfect when the expression is used directly in the field instead of pulling the value from the table.  P.S.-The original expression uses DateSerial to add five years.
It would still have been nice to see this "mysterious" expression...

;-)

Jeff
For your curiosity...

DateSerial(Year([Date Certified])+5,Month([Date Certified]),Day([Date Certified]))
;-)

OK, thanks

Jeff
Yes, these fields work fine for "existing" values, but when one of the needed values is not present, you get the #Type error.

Another way to avoid this is to set a Default Value for the "Date Certified" field.
ex:
Date()

;-)

Jeff