CODEWITHSUNDEEP
ms-accessdatabase-design
Hira Iftikhar
Hira Iftikhar

Reputation: 1

Access query calculation field based on date criteria

It is my first question at stack overflow. I want to ask if there is any way I could make a field in an Access query which will be based on two other fields? Eg. in the following case, I want to make a field ReqDate in the query which will return DueDate + 1 year if products > 90, and DueDate + 6 months if products < 90.

Example

Upvotes: 0

Views: 91

Answers (2)

Gustav
Gustav

Reputation: 55816

As this is Access, the query could look like:

Select 
    *, 
    DueDate, 
    products, 
    IIf(products > 90, DateAdd("yyyy", 1, DueDate), DateAdd("m", 6, DueDate)) As ReqDate 
From 
    YourTable;

or, if you like, a bit tighter:

Select 
    *, 
    DueDate, 
    products, 
    DateAdd("m", 6 * (Abs(products > 90) + 1), DueDate) As ReqDate 
From 
    YourTable;

Upvotes: 1

P. Jairaj
P. Jairaj

Reputation: 1033

To add date you can use the DATEADD function of sql server.

select ID, DueDate, products , 
(CASE 
    WHEN products > 90
        THEN DATEADD(yy, 1, DueDate)
        ELSE DATEADD(mm, 6, DueDate)

END) as ReqDate from table;

Links:

http://www.w3schools.com/sql/func_dateadd.asp

How do I perform an IF...THEN in an SQL SELECT?

Upvotes: 0

Related Questions