Calculating Value in a Column

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I have a table in Access. There are two date columns and another column which
is suppose to contain a number representing the difference between this to
dates.
Is this possible? If so, how do I do this?

Thank you.
 
Ayo said:
I have a table in Access. There are two date columns and another column
which
is suppose to contain a number representing the difference between this to
dates.
Is this possible? If so, how do I do this?

Thank you.
Click to expand...

I'm going to assume you mean the difference in days. Use the DateDiff
function:

diff = DateDiff("d", [Date1], [Date2])

Personally I wouldn't store this data in the table, just calculate it 'on
the fly' in a query:

Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference * From
TableName;
 
Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference *
From TableName;
Click to expand...

Sorry, a couple of typo's there. It should read:

Select [Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference From
TableName;
 
Thanks Stuart.
But the issue is that I need it to be on the Table. The people who are
going to be using this stuff want to see the two dates and the difference in
days. This was much easy in Excel, but since they want to use Access for
their data, they want to be able to do the same thing.
So, am I using "diff = DateDiff("d", [Date1], [Date2])" in the difference in
days column?

Stuart McCall said:
Ayo said:
I have a table in Access. There are two date columns and another column
which
is suppose to contain a number representing the difference between this to
dates.
Is this possible? If so, how do I do this?

Thank you.
Click to expand...

I'm going to assume you mean the difference in days. Use the DateDiff
function:

diff = DateDiff("d", [Date1], [Date2])

Personally I wouldn't store this data in the table, just calculate it 'on
the fly' in a query:

Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference * From
TableName;
Click to expand...
 
Thanks Stuart.
But the issue is that I need it to be on the Table. The people who are
going to be using this stuff want to see the two dates and the difference in
days. This was much easy in Excel, but since they want to use Access for
their data, they want to be able to do the same thing.
So, am I using "diff = DateDiff("d", [Date1], [Date2])" in the difference in
days column?


Stuart McCall said:
Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference *
From TableName;
Click to expand...

Sorry, a couple of typo's there. It should read:

Select [Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference From
TableName;
Click to expand...
 
Ayo said:
Thanks Stuart.
But the issue is that I need it to be on the Table. The people who are
going to be using this stuff want to see the two dates and the difference
in
days. This was much easy in Excel, but since they want to use Access for
their data, they want to be able to do the same thing.
Click to expand...

Showing a query instead of a table will achieve the same objective.
So, am I using "diff = DateDiff("d", [Date1], [Date2])" in the difference
in
days column?
Click to expand...

If you feel you really must store the calculation, then you'll have to
update the table with the calculated result first. I don't know what method
you're using to add/edit data in the table, but I'll assume DAO. When you
insert a new record, use the calc result to fill the field:

Set rs = CurrentDb.OpenRecordset("MyTableName")
rs.AddNew
rs!Date1 = <whatever>
rs!Date2 = <whatever>
rs!Difference = DateDiff("d", [Date1], [Date2])
rs.Update
Set rs = Nothing

If you're using a bound form to update the table, then use the BeforeUpdate
event to fill the field:

Me.FieldName.Value = DateDiff("d", Me.Date1.Value, Me.Date2.Value)
Stuart McCall said:
Ayo said:
I have a table in Access. There are two date columns and another column
which
is suppose to contain a number representing the difference between this
to
dates.
Is this possible? If so, how do I do this?

Thank you.
Click to expand...

I'm going to assume you mean the difference in days. Use the DateDiff
function:

diff = DateDiff("d", [Date1], [Date2])

Personally I wouldn't store this data in the table, just calculate it 'on
the fly' in a query:

Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference *
From
TableName;
Click to expand...
Click to expand...
 
Let the people refer to the query, rather than the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ayo said:
Thanks Stuart.
But the issue is that I need it to be on the Table. The people who are
going to be using this stuff want to see the two dates and the difference
in
days. This was much easy in Excel, but since they want to use Access for
their data, they want to be able to do the same thing.
So, am I using "diff = DateDiff("d", [Date1], [Date2])" in the difference
in
days column?


Stuart McCall said:
Select[Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference *
From TableName;
Click to expand...

Sorry, a couple of typo's there. It should read:

Select [Date1], [Date2], DateDiff("d", [Date1], [Date2]) As Difference
From
TableName;
Click to expand...
Click to expand...
 
Back
Top