Date/Time format for number of years 2

[Chris121]

Hi
When designing a table with a field that will contain a whole number between 1 and 5 for the number of years insurance cover bought with new purchase...

How should that field be formatted so queries can be run and that nuber of years can be simply added to an install date (standard format of shortdate) to calculate an expiry date.
Thanks

Chris.

 

[KenReay]

hi

If maximum value is five, you could define the column in a table as type number \ integer

see DateAdd() function in help for adding a number of Days, months, years etc to a given date

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lupins46]

ExpiryDate:dateadd("yyyy",5,[Install date])

 

[Chris121]

Hi Again
Almost got it working with formula below. If there is a date in the [Years Covered] it works fine, but if that field is empty, I get #Error as the result. Any Ideas?



Expr1: IIf([Customers]![Years Covered]=Null,Null,DateAdd("yyyy",[Customers]![Years Covered],[Customers]![ANN DATE]))

 

[KenReay]

Hi

DateAdd("yyyy",Nz([Customers]![Years Covered],0),[Customers]![ANN DATE]))

See Nz() function in help for explanation


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Chris121]

Thanks again Ken - no more #Error! however, it's not working quite as I wanted.
Pasting your formula above gave me a "to many parenthesis error" but I deleted a bracket and it works!!
Only thing is, where there was a null in [years covered], I wanted the new date field to be empty but now it just contains the original date.

I can live with that, but if it is easy to change it, would you let me know how?

Cheers.

Chris.

 

[PHV]

Expr1: IIf(IsNull([Customers].[Years Covered]), Null, DateAdd('yyyy',[Customers].[Years Covered],[Customers].[ANN DATE]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chris121]

Spot-on Perfect!

Thanks Ken - have another star - you are one!! ;-)

Chris.