Calculating a review date based on a date field and a non date field in a query (1 Viewer)

[Sweetnuff38]

I have the following fields in a query

Frequency (annual and semi-annual)
Review Date (Date Last Review)
Item (Item Reviewed)

I want to create a calculated field in the query that tells me the next review date based on whether it is annual or semi annual.

I can get the annual date with the following, just cant figure out the rest of the equation:

NextReviewDate: IIf([Frequency]="Annual","Semi_Annual",[ReviewDate]+366)

 

[boblarson]

I have the following fields in a query

Frequency (annual and semi-annual)
Review Date (Date Last Review)
Item (Item Reviewed)

I want to create a calculated field in the query that tells me the next review date based on whether it is annual or semi annual.

I can get the annual date with the following, just cant figure out the rest of the equation:

NextReviewDate: IIf([Frequency]="Annual","Semi_Annual",[ReviewDate]+366)

Try this:

NextReviewDate: Switch([Frequency]="Annual",DateAdd("y",1,[ReviewDate]),[Frequency]="Semi_Annual", DateAdd("m",6,[ReviewDate]))

 

[Sweetnuff38]

I have tried your suggestion Bob, the field name shows but no data is appearing?

 

[boblarson]

I have tried your suggestion Bob, the field name shows but no data is appearing?

Does Semi_Annual actually have the underscore in it?

 

[Sweetnuff38]

Sorry my bad! It works! Thanks Bob! Appreciate it!