By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Control source for Expiration date

P: 5
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!
Sep 27 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
8 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Assuming you have written the chemicals expiration date in a field dt_Expiration you could query it like so:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbl_Chemicals WHERE dt_Expiration is between Date() and DateAdd("yyyy",2,Date())
Sep 27 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
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.
Sep 27 '12 #3

P: 5
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.
Sep 28 '12 #4

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

Expand|Select|Wrap|Line Numbers
  1. =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.
Sep 28 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
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.
Sep 28 '12 #6

P: 5
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.
Sep 28 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
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.
Sep 28 '12 #8

P: 5
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 ;)
Sep 29 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.