Sort by a calculated date in a query

[Carol Frangipane]

I have a calculated date field in a query...if I try and sort by this field I get a data type mismatch.

[CONTREFF] is a date field in a table, [TERM] is a number field in a table. I am trying to calculate the year the contract expires in the "EndTerm" field. The calculation works fine, but I can't sort it.

EndTerm: DateSerial(Year([CONTREFF])+[TERM],Month([CONTREFF]),Day([CONTREFF]))

Please Help!!! Thank you ...

 

[Brianwarnock]

Do you have any null values in Contreff? These would flag as #error without the sort but with the sort you will get the message.

Brian

 

[Carol Frangipane]

You are a genius!!! I was banging my head against the wall...and all it was...was one miserable record with a NULL value! I should have figured that out! Thank you so so so so much!!!!!

 

[gemma-the-husky]

if you have a datefield, and you want to add term years its something like

dateadd("Y",term,mydate) - much easier than disassembling the date