Quarterly, Monthly, Annual advice

[cardgunner]

Need a little professional advice.

I have a product table. Those products expire over time. Their shelf life is monthly, quarterly, or annual.

What should I use for monthly, quarterly, and annual in my table.

My first thought was 30, 90, and 365. Then in my query I would use the datediff function. If the datediff was >= to ...then ...

However there are months with 28,29, and 31 days. With this quarterly can change as well. The annual as well by 1.

What is the rule, if there is one?

 

[boblarson]

Why don't you use Monthly, quarterly and annual?

The datediff will use "m" for month, "q" for quarter, and "yyyy" for year. No need to use a number.

 

[boblarson]

To expand upon that -

Use two fields to identify the expiration:

ExpPeriod - number (say 1 for 1 month)
ExpLength - Text (say m for 1 month)
StartDate - Date/Time

Then you can get the expiration by using this in a query:

ExpirationDate: DateAdd([ExpPeriod],[ExpLength],[StartDate])

or in a controlsource of a text box:

=DateAdd([ExpPeriod],[ExpLength],[StartDate])

 

[cardgunner]

I don't understand how that will help?

The owner of the db is going to run a report which tells him which products have expired but better yet what products are going to expire.

So in a underlying query I will have write my criteriato be datediff(d, [orderdt],date())<= ([expterm]+14)

This gets me all the records where the payterm has expired or will expire in 14days.

However say the product was purchased on Feb 4th. and it expires in a month. By using 30 that is not right, or is it?

or

if he bought it Jan 1st. and it expires in a month, where I have said that it was 30 days. I think I will be billing him a day early, or will I? Hmmmm.

Is there a way to bill by calender days? So I say a month which means the same day next month.

Is that what dateadd does. I never used it. Datepart i have, and datename.

 

[cardgunner]

I'm reading about dateadd now.

 

[boblarson]

Check out the sample I am posting. It will show you how you can use the table and the queries to create reports on the expirations using my method.

 

[cardgunner]

Yeah, I think that is better.

But is that right? Hmmm.

 

[cardgunner]

? Is that right?
I'll have to ask the owner of the db if he wants it x amount of days to = Y or use the days of the month.

In any case, thank you very much.

Not being a pest but any chance of looking at another post by me in forms???

 

[boblarson]

? Is that right?
I'll have to ask the owner of the db if he wants it x amount of days to = Y or use the days of the month.

You can use this process for either way. It is all in how you display it.

Not being a pest but any chance of looking at another post by me in forms???

Not sure if I'll either have the chance or, depending on the question, I might not want to. Normally, I will pick what I want to answer with some basis on where my mind is at the time. In other words, my day job is coding Access databases and sometimes my brain just doesn't want to get into too many in-depth problems at a time because I need to decompress. So, I may choose to pick certain ones to answer at certain times simply because it doesn't require much thought on my part.

 

[cardgunner]

I can understand that. Thanks for looking?