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
ExForm.PNG
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)
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)
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
;-)
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°)
(°v°)
ASKER
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]))
ASKER
With that I get an error that the expression cannot be used in a calculated column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<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...
You are still not posting the expression you are using...
ASKER
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
;-)
Jeff
ASKER
For your curiosity...
DateSerial(Year([Date Certified])+5,Month([Date Certified]),Day([Date Certified]))
DateSerial(Year([Date Certified])+5,Month([Date Certified]),Day([Date Certified]))
;-)
OK, thanks
Jeff
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
Another way to avoid this is to set a Default Value for the "Date Certified" field.
ex:
Date()
;-)
Jeff
iif(isnull([MyField],"",[M