Date Calculation

[jereece]

I have a table with 2 dates. Lets say they are "Assigned Date" and "Due Date". How can I use a query automatically make the "Due Date" 30 days after the "Assigned Date".

Thanks for the help. I frequently use the knowledge of the people on this forum.

Jim

 

[WayneRyan]

Jim,

You don't need to store the DueDate because you can always
calculate it.

In a query:

DueDate: AssignedDate + 30

or

DueDate: DateAdd("d", 30, [AssignedDate])

Wayne

 

[jereece]

But don't I have to select a Field and Table in which to base the query on? For example, I need my "Assigned Date" field to show that date. How can I add a AssignedDate + 30 query unless I add a "Due Date" field to the database?

Thanks again for the help.

 

[Tay]

Jim
You don't need to store the duedate at all. It's actually best not to store calculated dates as these often change, so it's better not to save the result.
What you need to do is create the query in design view, then in a new column in the query, type in what you want as WayneRyan said.

eg. DueDate: [tblwhatever]!AssignedDate + 30

Hope that made sense.

 

[jereece]

When I do this and try to run the query, I get the error message

"You can't set criteria before you add a field or expression to the field row"

So it wants a field and table name.

 

[Tay]

It sounds like you may have typed the expression in the wrong place (in the criteria bit). Type it where it says field. HTH.

 

[angshubh]

Put the Expression in critria area. It definitely works, I've checked. Angshuman
SQL:
SELECT SubscrptionPayments.Sub_No, [SubscrptionPayments]![EndDate]+30 AS DueDate
FROM SubscriberDetails LEFT JOIN SubscrptionPayments ON SubscriberDetails.Sub_No = SubscrptionPayments.Sub_No
GROUP BY SubscrptionPayments.Sub_No, [SubscrptionPayments]![EndDate]+30;

 

[Mile-O]

I'd use the DateAdd() function over just saying +30

i.e.

DateAdd("d", 30, [FirstDate])

Same result, however.