Ms access calculated field not updating
To specify the data type of a calculated field, wrap the calculation in CDate(), CLng(), CDbl(), CCur(), etc.: These conversion functions cannot handle Null. It allows you to output Nulls and still have Access recognise the data type correctly.
For calculated controls on a form or report, all you need do is set the Format property of the control.
Check out Database Normalisation and Table Structures.
It appears to me that the example you quote is exactly the sort of thing you need to consider avoiding assiduously, if you want to avoid the panoply of problems associated with non-normalised database design.
In general, you should think of the Format property as affecting how the data is presented to the user, not how it is stored in the database.
Nevertheless, if you set the Format property of a calculated control to a numeric or date type, Access will recognize and process the data correctly.; microsoft.support = microsoft.support