Calculating difference between FirstOF / LastOF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which logs various dates for a project, and the field name is
"Date_Of_Change". As multiple dates can occur for a project, I have a query
that uses Totals to identify the "Last" and "First" date for each project.
The query outputs these fields as "FirstOFDate_OF_Change" and
"LastOFDate_OF_Change".

I now want to use a calcuated Field to work out the difference between the
two dates, but as the original fields name are the same I can't use
Diff:[Date_OF_Change] - [Date_OF_Change] as this produces all 0's.

It also dosn't like using the Diff:[FirstOFDate_OF_Change] -
[LastOFDate_OF_Change] which is the name of the resulting fields.

Please can someone advise on the correct syntax to use?.


Many thanks

Winger
 
Firstly, you need to use Min and Max rather than First and Last to get the
earliest and latest date.

First just means return the first matching value found (regardless of
whether it is the earliest date), and Last means return the date in the last
row of the query (which will not be the latest date if the query is sorted
differently.)

You will then end up with fields that Access will call MinOfDate_Of_Change
and MaxOfOfDate_Of_Change. You can now get the difference between these 2
fields in another Field of your query with:
Diff: DateDiff("d", [MinOfDate_Of_Change], [MaxOfDate_Of_Change])
or possibly just:
Diff: [MaxOfDate_Of_Change] - [MinOfDate_Of_Change]

(BTW, since this is a Total query, make sure you use Expression in the Group
By row under this field, not Group By.)
 
Allen,

Diff: [MaxOfDate_Of_Change] - [MinOfDate_Of_Change] worked a treat.

Many thanks for such a swift response.

Regards

Winger



Allen Browne said:
Firstly, you need to use Min and Max rather than First and Last to get the
earliest and latest date.

First just means return the first matching value found (regardless of
whether it is the earliest date), and Last means return the date in the last
row of the query (which will not be the latest date if the query is sorted
differently.)

You will then end up with fields that Access will call MinOfDate_Of_Change
and MaxOfOfDate_Of_Change. You can now get the difference between these 2
fields in another Field of your query with:
Diff: DateDiff("d", [MinOfDate_Of_Change], [MaxOfDate_Of_Change])
or possibly just:
Diff: [MaxOfDate_Of_Change] - [MinOfDate_Of_Change]

(BTW, since this is a Total query, make sure you use Expression in the Group
By row under this field, not Group By.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Winger said:
I have a table which logs various dates for a project, and the field name
is
"Date_Of_Change". As multiple dates can occur for a project, I have a
query
that uses Totals to identify the "Last" and "First" date for each
project.
The query outputs these fields as "FirstOFDate_OF_Change" and
"LastOFDate_OF_Change".

I now want to use a calcuated Field to work out the difference between the
two dates, but as the original fields name are the same I can't use
Diff:[Date_OF_Change] - [Date_OF_Change] as this produces all 0's.

It also dosn't like using the Diff:[FirstOFDate_OF_Change] -
[LastOFDate_OF_Change] which is the name of the resulting fields.

Please can someone advise on the correct syntax to use?.


Many thanks

Winger
Click to expand...
Click to expand...
 
Back
Top