CODEWITHSUNDEEP
sqlms-access
Filipa
Filipa

Reputation: 35

Query that calculates a DateDiff returns a Data Type Mismatch in criteria

I'm trying to create a query that returns the products that have less than a month until their expiration date. In one column I'm using a DateAdd to, based in the receive date of the product, know when is the expiration date. Then in another column I'm using a DateDiff to calculate the days until the expiration date so that I could apply a criteria of <30. The problem is that the query runs normally but in the moment that I write <30 in Criteria, it gives me a data type mismatch error. I've tried a bunch of different things to avoid this, such as write in criteria of DateAdd <Date() + 30, or do another query where I calculate the second part, or put the <30 in the expression builder so that it returns a 0 or -1, but even with that result, I can't use a criteria to have just the results that I want, but nothing worked.

Product table | ProductName | EstimatedDuration | | ----------- | ----------------- | | A | 365 | | B | 30 |

People Table: | Person | ProductName | ReceiveDate | | ------ | ----------- | ----------- | | Name1 | A | 12-05-2020 | | Name2 | B | 26-08-2020 |

My first intention is to create a query that calculates the Expiration Date, based in ReceiveDate and EstimatedDuration. After that I want to know all the products that have their expiration date in the next month for example.

The SQL code is:

SELECT [Products].[ProductName], [People].[ReceiveDate], [Products].EstimatedDuration, DateAdd("w",[Products].[EstimatedDuration],[People].[ReceiveDate]) AS ExpirationDate, DateDiff("d",Date(),[ExpirationDate]) AS Days
FROM [Products] INNER JOIN [People] ON [Products].ProductName= [People].[ProductName]
WHERE ((([Products].EstimatedDuration) Is Not Null)) AND ((DateDiff("d",Date(),[ExpirationDate]))<30));

The WHERE condition is because in my product table I don't have EstimatedDuration in all products.

Thank you in advance for your help!

Upvotes: 0

Views: 358

Answers (1)

Gustav
Gustav

Reputation: 55831

You may need the extended syntax:

SELECT 
    [Products].[ProductName], 
    [People].[ReceiveDate], 
    [Products].EstimatedDuration, 
    DateAdd("w",Nz([Products].[EstimatedDuration], 0),[People].[ReceiveDate]) AS ExpirationDate, 
    DateDiff("d",Date(),[ExpirationDate]) AS Days
FROM 
    [Products] 
INNER JOIN 
    [People] ON [Products].ProductName= [People].[ProductName]
WHERE 
    [Products].EstimatedDuration Is Not Null 
    AND 
    DateDiff("d",Date(), DateAdd("w",Nz([Products].[EstimatedDuration], 0), [People].[ReceiveDate])) < 30;

Upvotes: 1

Related Questions