Calculation Field Issues

[MintChipMadness]

Hello Everyone,



I was hoping someone could help me figure out an issue I am having. I attached my database. I have 4 fields which are [RetentionType], [RecordFYE], [Retention Period], and [Destruction Date]. The calculation field is the Destruction date. Basically what the calculation field does is take the [RecordFYE] field and add The [Retention Period] to the year and then show the date. What I would like it to do is use an IFF statement using the [RetentionType] field. If the the [RetentionType] is "Permanent" it would say "N/A", if not it would do the calculation. I will list my calculation and what I tried below. I got the error "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server." Thank you for your help.

DateSerial(Year([RecordFYE])+[Retention Period],Month([RecordFYE]),Day([RecordFYE]))

what I tried to get it to work

IIF([RetentionType] = "Permanent", "N/A", DateSerial(Year([RecordFYE])+[Retention Period],Month([RecordFYE]),Day([RecordFYE])))

[JoeM]

Due to security reasons, I cannot download any databases. Can you list a few of your RecordFYE and Retention Period field values. Also, let us know what Format that are in.

By the way, what are you ultimately trying to do with this RetentionType field? If you are trying to store it in a date field, or use it in other calculations, I think that "N/A" may give you some problems.

[MintChipMadness]

Due to security reasons, I cannot download any databases. Can you list a few of your RecordFYE and Retention Period field values. Also, let us know what Format that are in.

By the way, what are you ultimately trying to do with this RetentionType field? If you are trying to store it in a date field, or use it in other calculations, I think that "N/A" may give you some problems.

Thank you for the the Reply JoeM. I listed the Data Types and examples of entries below. From your reply I am guessing it might be a data type issue? Should I make them all text to make them work together?

Field Info:
[RetentionType] =Text (Permanent or Temporary)
[RecordFYE] = Date/Time (e.g. 5/30/2010)
[Retention Period] = Number (in years, and example is 8)
[DestructionDate] = Calculated (solution is a date)

[RayMilhon]

I would like to say that I have never, I repeat never found a reason to use a calculated field in a table. Put the calculation in a query.

[MintChipMadness]

I would like to say that I have never, I repeat never found a reason to use a calculated field in a table. Put the calculation in a query.

Thank you for your reply RayMilhon. If you were going to create a query, how would you write the expression? I am open to all solutions.

[JoeM]

[DestructionDate] = Calculated (solution is a date)

That is your problem right there, then. You cannot place "N/A" in a date field.

Ray is right. I missed that in your original post. I thought you were just trying to do a Calculated Field in a query, and missed that you were trying to write the results back to a table (which, you cannot store formulas in a table anyway). You would just have your calculated field in a query and use that query for whatever you need, i.e.

DestructionDate:IIF([RetentionType] = "Permanent", "N/A", DateSerial(Year([RecordFYE])+[Retention Period],Month([RecordFYE]),Day([RecordFYE])))

[MintChipMadness]

That is your problem right there, then. You cannot place "N/A" in a date field.

Ray is right. I missed that in your original post. I thought you were just trying to do a Calculated Field in a query, and missed that you were trying to write the results back to a table (which, you cannot store formulas in a table anyway). You would just have your calculated field in a query and use that query for whatever you need, i.e.

DestructionDate:IIF([RetentionType] = "Permanent", "N/A", DateSerial(Year([RecordFYE])+[Retention Period],Month([RecordFYE]),Day([RecordFYE])))

Thank you!!! I tried your code in a query and it worked perfectly.

[JoeM]

Thank you!!! I tried your code in a query and it worked perfectly.

Its actually just the original code you had. I just said to place it in a query (like Ray did) and gave it a name (alias).
So you had what you needed all along, just didn't have it in the right place!