Date calculation Problem

[gots]

Dear Friends,

I am into a situation where I have to calculate Warranty Date and accordingly set the warranty Status in the subform with fields as below,

tblProductDetails
- ProductNameID -Autonumber - PK
- CustomerID - FK (linked to tblcustomerdetails)
- SerialNo
- InvoiceNo
- Invoice Date
- Installation Date
- Warranty End Dt - formula used in form: =IIf(DateAdd("m",6,[Installation Date])>DateAdd("m",8,[Invoice Date]),DateAdd("m",8,[Invoice Date]),DateAdd("m",6,[Installation Date]))
- Warranty Status - formula used in form: =IIf(Date()<=[Warranty End Dt],"IN WARRANTY","OUT OF WARRANTY")
- Remarks

I am having a Problem that if there is no data in [Warranty End Dt] then there should be no status in [warranty status].

I am not expert in expression or coding and somehow by trial and error I managed this formulas. Hence please assist me.

 

[KenHigg]

=iif([Warranty End Dt] & "" = "","N/A",IIf(Date()<=[Warranty End Dt],"IN WARRANTY","OUT OF WARRANTY"))

???

 

[gots]

Firstly thank you for your assistance. My problem is solved.

Can you please explain a little about the first part of formula you entered so as I can learn
1. What is & used for
2. Why "=" is used

Is this called nested IF statement.

Please also justify whether putting this formulas in the subform control will be viable to get a monthly /yearly report for all the warranties which are expiring in the selected period.

Please also let me know that even If I delete the record in customer the subform data stays in the product detail subform Why is that and how to prevent the same. (I have set the relationship between two tables as Enforce referential intrigity: ON , Cascade update records: ON , Cascade delete records: ON and Join type as Include all records from product details and only those records from customer details where the joined fields are equal.

 

[MSAccessRookie]

gots,

I might be able to assist with some of the questions, and the rest of the comments I leave for others.

Can you please explain a little about the first part of formula you entered so as I can learn
1. What is & used for?

The "&" character is the standard concatenation command used byMS Access

2. Why "=" is used?

The = character is used because you are testing if the value of the Warrenty End date is not entered (NULL).

Note that IsNull([Warranty End Dt]), or ([Warranty End Dt] IS NULL)
will have the same effect as ([Warranty End Dt] & "" = "")

Is this called nested IF statement?

Yes, this is an example of a Nested iif Statement.

 

[KenHigg]

The '&' is used to join two text strings, also known as to concatenate. The = was used to see if the two side were equal.

The field can be 'null', have a zero length string, or have a valid value otherwise. In your case, it could be null or have a vaild date value. What we needed to do was to check and see if had a null. You could have checked this several other ways, what I did was use kind of a cheat way. It took the value, whatever it was and added (concatenated) it with a zero length string. So when it tested it with the '=' and if it equaled a zero length string, we knew the original value must have been a null value in which case we wanted to set the value to 'n/a'., etc...

 

[gots]

Thank you all for the assistance. Now I am thinking to put the formulas in Query so as I can generate reports at later stage, but I don't to how to make a Query, please suggest.

 

[boblarson]

but I don't to how to make a Query, please suggest.

Then you need this:

http://www.functionx.com/access2003/Lesson17.htm

 

[gots]

Dear Super Moderator,

Thank you for your assistance and a very beautiful link to learn many things. I will be reffering the same.

 

[KenHigg]

'beautiful link'

 

[gots]

Thank you for pointing out my mistake

Dear Kenhigg,

Thank you for pointing out my mistake and your assistance.

 

[KenHigg]

gots, Just ribbing you about your 'english'. Glad you have your MS Access issue sorted out.