Receiving Error when creating Calculated Field

[hydrojoe11]

Greetings

I'm attempting to create a calculated field in a query called 'Test'. It's purpose is to determine if a certain field has a 'c' for closed and then returns the appropriate 3 months in a concatenated string. This previously worked when it was shorter. Now that I extended it for 2011 fields i'm getting an error that it is too long. I'm pasting the below code into the 'field' line of my query. Any help would be great.

Test: IIf([DEC12_Status]="c",[OCT12_A] & " " & [NOV12_A] & " " & [DEC12_A]
,IIf([NOV12_Status]="c",[SEP12_A] & " " & [OCT12_A] & " " & [NOV12_A],
IIf([OCT12_Status]="c",[AUG12_A] & " " & [SEP12_A] & " " & [OCT12_A],
IIf([SEP12_Status]="c",[JUL12_A] & " " & [AUG12_A] & " " & [SEP12_A],
IIf([AUG12_Status]="c",[JUN12_A] & " " & [JUL12_A] & " " & [AUG12_A],
IIf([JUL12_Status]="c",[MAY12_A] & " " & [JUN12_A] & " " & [JUL12_A],
IIf([JUN12_Status]="c",[APR12_A] & " " & [MAY12_A] & " " & [JUN12_A],
IIf([MAY12_Status]="c",[MAR12_A] & " " & [APR12_A] & " " & [MAY12_A],
IIf([APR12_Status]="c",[FEB12_A] & " " & [MAR12_A] & " " & [APR12_A],
IIf([MAR12_Status]="c",[JAN12_A] & " " & [FEB12_A] & " " & [MAR12_A],
IIf([FEB12_Status]="c",[DEC11_A] & " " & [JAN12_A] & " " & [FEB12_A],
IIf([JAN12_Status]="c",[NOV11_A] & " " & [DEC11_A] & " " & [JAN12_A],
IIf([DEC11_Status]="c",[OCT11_A] & " " & [NOV11_A] & " " & [DEC11_A],
IIf([NOV11_Status]="c",[SEP11_A] & " " & [OCT11_A] & " " & [NOV11_A],
IIf([OCT11_Status]="c",[AUG11_A] & " " & [SEP11_A] & " " & [OCT11_A],
IIf([SEP11_Status]="c",[JUL11_A] & " " & [AUG11_A] & " " & [SEP11_A],
IIf([AUG11_Status]="c",[JUN11_A] & " " & [JUL11_A] & " " & [AUG11_A],
IIf([JUL11_Status]="c",[MAY11_A] & " " & [JUN11_A] & " " & [JUL11_A],
IIf([JUN11_Status]="c",[APR11_A] & " " & [MAY11_A] & " " & [JUN11_A],
IIf([MAY11_Status]="c",[MAR11_A] & " " & [APR11_A] & " " & [MAY11_A],
IIf([APR11_Status]="c",[FEB11_A] & " " & [MAR11_A] & " " & [APR11_A],
IIf([MAR11_Status]="c",[JAN11_A] & " " & [FEB11_A] & " " & [MAR11_A],
"ERROR"))))))))))))))))))))))

[alansidman]

Looking quickly at what you have, it appears that you may have a normalization issue. It appears that you have a field for each month. You should really have a field for the month and one for the year.

Here is a link on data base design in a relational data base that should help you to sort the normalization issue which uncomplicate your expression in your query.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[hydrojoe11]

Thanks for the reply. I agree with your statement. The table in which this information is 'housed' wasn't designed by myself, but rather kept by our IT department. I'm trying to look for a 'creative but simple' way to work within the constraints.

[hydrojoe11]

I was thinking maybe it could be possible to create a VBA function that I could pass the values to a calculated field? I have limited experience with that, but trying to brainstorm here.

[alansidman]

Here's a link on VBA IF-Then-Else

http://www.techonthenet.com/access/f...ed/if_then.php