CODEWITHSUNDEEP
ms-access
Scott Jackson
Scott Jackson

Reputation: 67

MS Access : How do I update a field using two other fields?

I've an access database table with 3 fields :

Warranty time has 1, 2, 3, 4, 5 in it which corresponds to years.

How can I auto-populate the 'Warranty expiry' field (which is a date field) by looking at the 'Purchase date' and then adding on the 'Warranty time' (warranty time will be 1 = 365 days, 2 = 730 days, etc)?

Upvotes: 3

Views: 1573

Answers (2)

HansUp
HansUp

Reputation: 97101

Do you have a compelling need to actually store values for Warranty expiry?

The reason I asked is because storing derived values is generally considered bad practice. You could easily derive Warranty expiry from Purchase date and Warranty time whenever you need it. If you store Warranty expiry, you need to make sure the value gets updated whenever Purchase date and/or Warranty time change. You can use methods to minimize the risk the values can get out of sync, but you don't need any of those extra efforts if you're not storing the value in the first place.

Upvotes: 5

Fionnuala
Fionnuala

Reputation: 91356

Do not use days, use DateAdd function or DateSerial

DateSerial(Year(PurchaseDate)+WarrantyTime, Month(PurchaseDate), Day((PurchaseDate))

DateAdd("yyyy", WarrantyTime, PurchaseDate)

See: http://www.techonthenet.com/excel/formulas/dateadd.php

Upvotes: 8

Related Questions