DateDiff function

[stavros]

I am trying to calculate how many days are left till end of contract...



whats wrong with this formula

=DateDiff("d",[Completion_Date], Date())

[rzw0wr]

I am not sure.
I do know that this would be better done in a query.
Calculated fields can cause problems later on in your development of your data base.

Dale

[stavros]

I am not sure.
I do know that this would be better done in a query.
Calculated fields can cause problems later on in your development of your data base.

Dale

it doesn't work in query either...

Time_left: DateDiff("d",[Completion_Date], Date())


any help?

[ssanfu]

Would you post the SQL of the query? The picture doesn't show the expression.

This worked for me:

SELECT ImportTest.Text1, ImportTest.Text2, ImportTest.Date_Time, DateDiff("d",[date_time],Date()) AS TimeLeft
FROM ImportTest;

With the [Date_Time] = 5/1/2013, and Date() =5/31/2013 (today), the result is 30.

[stavros]

SELECT Contracts.Contract_ID_No, Contracts.Contract_Name, Contracts.Start_Date, Contracts.Completion_Date
FROM Contracts;

This formula doesn't work... Time_left: DateDiff("d",[Completion_Date], Date())

[orange]

How is completion_date defined? Is it date/Time datatype?
It seems you don't need the equal signs for a calculated field.

[stavros]

completion_date is defined as date/time (Short date)...

[orange]

Did you try removing the equal(=) sign?

[stavros]

i am using access only several times, learning from youtube, so please be patient :-)

where is it equal(=) sign in this Time_left: DateDiff("d",[Completion_Date], Date())

[orange]

I was suggesting you remove the equal sign when using the function as a calculated field in your table.

I don't understand why your expression is not working in the query.

[ssanfu]

I made a table using the fields in your SQL, then added a couple of records. I used your query and added the date diff function. No errors....

field name - field type
----------------------
Contract_Name - text
Start_Date - date/time
Completion_Date - date/time



Here is the query:

SELECT Contracts.Contract_ID_No, Contracts.Contract_Name, Contracts.Start_Date, Contracts.Completion_Date, DateDiff("d",[Completion_Date],Date()) AS Time_left
FROM Contracts;

Create a new query, switch to SQL view, paste in the above SQL statement and execute the query.

[stavros]

the query with past text from ssanfu post is working. only for contracts that are not finished jet time left is negative number while contracts that are finished left time is positive number... for those past contract it would be better that is written contract finished... and left time should be positive number

[ssanfu]

Not sure what you are asking...

How do you tell if a contract is finished?

If the contract is not finished, and you want the number to be positive, swap the dates:

DateDiff("d",Date(),[Completion_Date]) AS Time_left

[stavros]

Not sure what you are asking...

How do you tell if a contract is finished?

If the contract is not finished, and you want the number to be positive, swap the dates:

DateDiff("d",Date(),[Completion_Date]) AS Time_left

thx. done by my self... now i have to do automatic email those persons which contract is expiring within 45 days and repeat within 30 days...

SELECT Contracts.Contract_ID_No, Contracts.Contract_Name, Contracts.Start_Date, Contracts.Completion_Date, IIf(DateDiff("d",Date(),[Completion_Date])<0,"contract expires",DateDiff("d",Date(),[Completion_Date])) AS Time_leftFROM Contracts;

[stavros]

for one contract it can be several contractors (joint venture)... while in query same contract is repeating for each contractor in JV.

how could i change it that one contract in written only once and in column contractors is listed several firms?