Expiry Date (1 Viewer)

[majid.pervaiz]

Dear Expert friends,

i would like to ask one question, I have multiple dates in one table and i would like to check if one of the date is going to expire in a month then only the query will show the result.

Can someone please advise how to do that.

 

[plog]

I have multiple dates in one table

I fear this means you have multiple date columns in your table. For example:

CustomerID, MilkExpiration, FruitExpiration, MeatExpiration, ...
14, 2/1/2018, , 3/15/2018, ...
17, 4/5/2018, 1/19/2018, , ...

That is not the correct table structure for this. Instead your table should accomodate that data vertically:

CustomerID, ExpirationItem, ExpirationDate
14, Milk, 2/1/2018
14, Meat, 3/15/2018
17, Milk, 4/5/2018
17, Fruit, 1/19/2018

With data structured like that, finding expirations within a month is super simple (well, after you define what 'in a month means') :

SELECT * FROM YourTableNameHere WHERE (ExpirationDate > Date()) AND (ExpirationDate <= (Date() +30))

 

[arnelgp]

You add a critera

DateDiff("d", date, datefield)<=30

 

[Mark_]

If you have seven dates, you can create seven queries, one for each.

Each query would be Primary Key and ExpDate: IIF( DateToCheck > DATE() AND DateToCheck < DATE()+30), DateToCheck, NULL)

Then you can do a UNION query of all 7 tables to get a list of ONLY those expiration dates within a month (assuming you wish a rolling 30 days for the month).

From that UNION query, make another that links back to the records you want, but look for ONLY records with an entry in your query that is not null.

From this description, you can see why plog suggests normalizing your data instead. It CAN be done but isn't easy. It also doesn't work well when you discover you have other dates you need to check.