Control source for Expiration date

[hatsepsut]

Hi,
I am trying to modify a database that has a form with chemicals,manufacturing date and expiration date. They want the expiration date to be automatic so I used Dateadd in the field. However, in the report, I have no idea how to set the coding like if they ask for a chemical with 2 years expiration, that will show. I can provide additonal info if needed.
Thanks in advance!

[TheSmileyCoder]

Assuming you have written the chemicals expiration date in a field dt_Expiration you could query it like so:

SELECT * FROM tbl_Chemicals WHERE dt_Expiration is between Date() and DateAdd("yyyy",2,Date())

[zmbd]

Hello and Welcome

Provided you haven't stored the information in a table already....

In a report you'd set the record source of an ubound control to the calculation: =Nz(DateAdd('yyyy',6,[manfdate]),"Missing ManfDate!") this adds 6 years to the date shown in the bound control [manfdate] or returns a the mesage. You can also use the IIF or SWITCH branchs too.

I typically do this in the query doing the same thing in a calculated field except I return an error, thus if the expiry is different (6mo for one, 1yr for another etc) for each product I can use the information from the table.

[hatsepsut]

Thanks for the replies. I think I need to provide more information.
The report is actually a certificate of analysis wherein it extracts chemical from form and the expiration date is dependent on the type of chemical. We only have 2 expiration. One is after 6 months and the other is after 2years. The database was really complex and as much as possible I want to resolve this using an unbound field in a report. I tried doing IIf but somehow I just cant make it work. I'll post the IIf statement that I did.

[hatsepsut]

I put this in the control source of the field named "Expiration" on the rpt:

=IIf([ProductName]="T-8 Conc.","DateAdd("m",6,[Date])","DateAdd("yyyy",2,[Date]"))

T-8 Conc. is one of the chemical with 6 months expiration.There are only 3 chemicals with this expiration and the rest are all 2 years. Thanks for any input.

[zmbd]

Ok,
You can start hard-coding these chemicals in a bunch of switch conditions or IIF functions on the report which means you'll have to maintain this for every product name change (or if you're lucky, just when product ids need to be added, deleted, modified, etc...)

and

someone like myself that inherits these "broken" databases will "Bless" you greatly in the future...

OR

You need to back to the data tables and look at where the expiration information is stored, if at all. If it is already stored... HURRAY, you can use that information in your calculated form!

IF not... then you either need to add a table (yuck) or add a single field to the product table storing the expiration... I'd use months... for the compounds. Once done, then use this information in the control.

[hatsepsut]

Hi ZMdb,

Thanks a lot! Somehow I was able to get the IIf statement working. So happy I don't need to edit any macros or add any tables or queries.

[zmbd]

I'm glad that you've got this to work...

Two things...
The IIF is (IMHO) not the best long term solution. You'll run into the issue soon enough.

Second... [Date]
I highly advise you to change this field name.
"Date" is a reserved word in VBA/SQL and it will eventually cause you issues.
I've never figured out why the table designer doesn't simply disallow the reserved word usage instead of relying on the user to get this information from the help files, classes, or other programmers.

[hatsepsut]

Thanks for the feedback. I foresee I'll have problems soon but since this is a bit urgent, we'll use this first and I'll think of how I can modify some of the field names. I really have issue with that [Date].
Thanks again ;)