DateDiff

[adamides]

I want to calculate the difference between two date which are stored in two different tables and have the result shown in a form text-field.
I tried using the DateDiff function with no success - errors missing operands or operator etc.
Can anybody help?

 

[mjbtx]

Please post more info on your fields. What is the formula you are using that is giving the error?

DateDiff should work just fine. Make sure you are using the right syntax as follows:

DateDiff("d", [1stDateField], [2ndDateField])

where "d" = days or any other operator you might want to calculate.

 

[adamides]

Thanks for the replies but I still did not figure it out. I have two tables Performance
and Matings. In Performance I have a field GestationLenght which basically I want to have the difference (in days) between mating and birth. The matingdate is in the Matings tables and the birthdate is in the Performance table.
I have written this query using DateDiff but it doesn't work.
Please see the code below.

--
SELECT Performance.GestationLength, DateDiff([GestationLength],[KiddingDate],[MatingDate]) AS Expr1
FROM Matings INNER JOIN Performance ON Matings.FemaleAnimalCode = Performance.FemaleAnimalCode;

 

[mjbtx]

I am posting my last email to you so everyone else can see the answer as well in case anyone needs the info.

One main problem you have here is you are not telling your query what you want your time calculated in.

You gave your query as:

SELECT Performance.GestationLength,
DateDiff([GestationLength],[KiddingDate],[MatingDate]) AS Expr1 FROM Matings INNER JOIN Performance ON Matings.FemaleAnimalCode = Performance.FemaleAnimalCode;

However the formula for DateDiff is: DateDiff("d", FirstValue, SecondValue) where "d" = days but you could also substitute other time variables.

Access doesn't recognize [GestationLength] as a date qualifier.

Therefore, structure your query as:

SELECT DateDiff("d",[KiddingDate],[MatingDate]) AS GestationLength FROM Matings INNER JOIN Performance ON Matings.FemaleAnimalCode = Performance.FemaleAnimalCode;

This will give you a number of days. If you want to update the GestationLength field in the table with this information then you would use an update query with the syntax of:

UPDATE Matings INNER JOIN Performance ON Matings.FemaleAnimalCode = Performance.FemaleAnimalCode SET Performance.GestationLength = DateDiff("d",[KiddingDate],[MatingDate]);

So, in closing, if you want to store the value of days between these dates in your table, you would use the update query. If you are just going to use this value in a report or query, then just use the select query above.

Hope this helped you!

Michael

[This message has been edited by mjbtx (edited 01-16-2002).]