Calculate Warranty Expirary Date

[scouser]

Back on the development trail............

So what do I want to achieve?
I have a form frmHardwareEntry based on a query qryHardwareEntry which in turn is based on a table tblHardware.
I have a field in table hardware 'Date Purchased'.

Within the frmHardwareEntry I would like to add a field(s) 'Warranty' & 'Warranty Expirary Date'. So user enters date purchased, the manufacturers warranty period (1,2,3,4 or 5 years) then 'Warranty Expirary Date' auto populates...ooohh and an additional field that counts down the remaining time......'Warranty Expirary Date minus Date()'?

Advice warmly received. The DB is intended for the forum for others to make use of (currently on version 999!!!!). Will post when ironed out license allocation issue in the mean time thought the above would be a nice inclusion!
Thanks,
Phil.

 

[boblarson]

Back on the development trail............

So what do I want to achieve?
I have a form frmHardwareEntry based on a query qryHardwareEntry which in turn is based on a table tblHardware.
I have a field in table hardware 'Date Purchased'.

Within the frmHardwareEntry I would like to add a field(s) 'Warranty' & 'Warranty Expirary Date'. So user enters date purchased, the manufacturers warranty period (1,2,3,4 or 5 years) then 'Warranty Expirary Date' auto populates...ooohh and an additional field that counts down the remaining time......'Warranty Expirary Date minus Date()'?

Advice warmly received. The DB is intended for the forum for others to make use of (currently on version 999!!!!). Will post when ironed out license allocation issue in the mean time thought the above would be a nice inclusion!
Thanks,
Phil.

First, you can calculate the Expiration date by using DateAdd

Me.WarrantyExpDate = DateAdd("yyyy", Me.NumberOfYears, Me.PurchaseDate)

Second, you DO NOT want to store the expiration date. You can calculate that on the fly at any time with the purchase date and the number of years for warranty. Just use the same DateAdd formula for displaying the expiration date, queries, reports, etc.

 

[scouser]

Reply

Bob speedy reply many thanks. I will have a play about and post back.
Many Thanks,
Phil.

 

[Rabbie]

Try this

Expirydate = dateadd("yyyy",warrantylength,Purchasedate)
Remainingtime = Expirydate - Date()

HTH

Bob

Snap!

Just cos you type faster than me!

 

[scouser]

Speedy

Thanks guys. It has been a while since I did any access work.
Do I add an unbound field to allow input for the warranty length (yyyy)? I would then add the code in Rabbie post to an unbound field on the form to calculate the expirary date? Currently only have DatePurchased field?
Many Thanks,
phil.

 

[boblarson]

I would add a field for number of years (if you don't have it) on the warranty and then use an unbound text box to display the warranty expiration. No need to store it, and unless there's a compelling need to do so, it follows normalization rules much better.

 

[scouser]

Warranty

Hi Bob. Are you suggesting I create a seperate table to hold warranty information or add a new field to my tblHardware? I must admit my tblHardware could be a little better designed!! It holds data for printers, switches, monitors etc....seperate table for monitors would be good!!

Skipped ahead.......added a new field to tblHardware 'WarrantyPeriod' and set DataType as Number (Integer) initial value 1.......
Thanks,
Phil.

 

[boblarson]

Without seeing your entire design I can't tell you totally for sure, but if you have the purchase date, it may be possible to go ahead and store the warranty period with it. I don't know that you would NEED another table, but depending on what else might need to be associated with it, it might be necessary.

 

[scouser]

Nearly There?

OK. Have added new field to form 'WarrantyPeriod' (now NO default value). I input 3 (for 3 years). I have an unbound field on the form:

Expirydate = dateadd("yyyy",WarrantyPeriod,DatePurchased)

This displays as #Name............. so basically I have done it wrong!! Probably the DataType / Format for WarrantyPeriod?
Phil.

 

[boblarson]

Expirydate = DateAdd("yyyy", Me!WarrantyPeriod, Me!DatePurchased)

also, the format for the warranty period should be DOUBLE.

 

[scouser]

Be Patient With Me!!

Bob be patient with me!!

Still getting #name? in unbound field:
'Expirydate = DateAdd("yyyy", Me!WarrantyPeriod, Me!DatePurchased)'

DatePurchased (Date/Time) = 01/08/2007
WarrantyPeriod (Number) = 3
Expiry Date = #Name?

Now both you and I know it is ME....hope above helps narrow it down!!
Many Thanks,
Phil.

 

[boblarson]

Bob be patient with me!!

Still getting #name? in unbound field:
'Expirydate = DateAdd("yyyy", Me!WarrantyPeriod, Me!DatePurchased)'

DatePurchased (Date/Time) = 01/08/2007
WarrantyPeriod (Number) = 3
Expiry Date = #Name?

Now both you and I know it is ME....hope above helps narrow it down!!
Many Thanks,
Phil.

I really need to get to bed - it's 3AM here. Can you post to make it quicker to solve?

 

[scouser]

Post

Bob give me 5 minutes to strip down to fit on forum!!
Phil.

 

[scouser]

Db

Bob see attached, hope I caught you in time!!
Phil.

 

[boblarson]

Bob see attached, hope I caught you in time!!
Phil.

Sorry, it took too long as I really had to go get sleep and you were over the 5 minutes (I just couldn't wait any longer). You have the control source wrong. I was thinking code and not control source so I gave you slightly incorrect syntax. In the control source use:

=DateAdd("yyyy",[WarrantyPeriod],[DatePurchased])

And to get rid of the #Name for anything which doesn't have entries in either of those fields, ACTUALLY change the control source to this:

=IIf(IsNull([WarrantyPeriod]) Or IsNull([DatePurchased]),"",DateAdd("yyyy",[WarrantyPeriod],[DatePurchased]))

 

[scouser]

Resolved

Bob many thanks for your time (not forgetting Rabbie). That has worked a treat, will sort out the licensing issue (will ask a programmer this end to have a look!!!) then post to sample DB's.

Hope you had a better nights sleep than me, dam wind playing a tune through a dodgy window seal!!!!

Many Thanks,
Phil.

 

[vdanelia]

Thank you for help....

 

[vdanelia]

I worked hard but cannot do my best!

I wanted to calculate Warranty expiration date by Year, month and days..
I have an option to choose Warranty Type (Year, Month, Day)
I made a test database and please help me with is....

Here is the Download link http://dl.dropbox.com/u/6200480/Data.accdb

Thank You Very Much In Advanced

 

[vdanelia]

Somebody Please help!

 

[Rabbie]

Read the link in my signature to learn how to get your query answered. You'll find impatient screaming for help on a Sunday just doesn't get people to help you.