Formula Help

[Guest]

I have a table with a Colum labeled "date arrived" and "date returned." I
have an empty colum with "Number of Days." How do I make number of days
automatically populated useing the other two fields.

Everytime I try and add a formula to the cells I get an error: "The Value
you entered isn't valid for this field.

Thank you.

 

[fredg]

I have a table with a Colum labeled "date arrived" and "date returned." I
have an empty colum with "Number of Days." How do I make number of days
automatically populated useing the other two fields.

Everytime I try and add a formula to the cells I get an error: "The Value
you entered isn't valid for this field.

Thank you.

Not only you should not do this in a table, you cannot do this in a
table.

Here is one way to get the NumberOfDays difference.
Create a query.
Include the fields you need, but NOT the "number of days" field (that
field should be deleted from the table).
Add a new column:
NumberOfDaysateDiff("d",[DateArrived],[DateReturned])

You can use this new field in any report.

Or you can, in a Form or in a Report, add an unbound control to the
form/report.
Set it's control source to:
=DateDiff("d",[DateArrived],[DateReturned])

Again, this calculated control should not be saved in any table.
Whenever you need the data, re-calculate it.

 

[Guest]

thank you...you rock

I have a table with a Colum labeled "date arrived" and "date returned." I
have an empty colum with "Number of Days." How do I make number of days
automatically populated useing the other two fields.

Everytime I try and add a formula to the cells I get an error: "The Value
you entered isn't valid for this field.

Thank you.

Not only you should not do this in a table, you cannot do this in a
table.

Here is one way to get the NumberOfDays difference.
Create a query.
Include the fields you need, but NOT the "number of days" field (that
field should be deleted from the table).
Add a new column:
NumberOfDaysateDiff("d",[DateArrived],[DateReturned])

You can use this new field in any report.

Or you can, in a Form or in a Report, add an unbound control to the
form/report.
Set it's control source to:
=DateDiff("d",[DateArrived],[DateReturned])

Again, this calculated control should not be saved in any table.
Whenever you need the data, re-calculate it.

 

[Guest]

Any chance you would also know how to calculate the number of items linked to
one contact?

I have a table with a Colum labeled "date arrived" and "date returned." I
have an empty colum with "Number of Days." How do I make number of days
automatically populated useing the other two fields.

Everytime I try and add a formula to the cells I get an error: "The Value
you entered isn't valid for this field.

Thank you.

Not only you should not do this in a table, you cannot do this in a
table.

Here is one way to get the NumberOfDays difference.
Create a query.
Include the fields you need, but NOT the "number of days" field (that
field should be deleted from the table).
Add a new column:
NumberOfDaysateDiff("d",[DateArrived],[DateReturned])

You can use this new field in any report.

Or you can, in a Form or in a Report, add an unbound control to the
form/report.
Set it's control source to:
=DateDiff("d",[DateArrived],[DateReturned])

Again, this calculated control should not be saved in any table.
Whenever you need the data, re-calculate it.

 

[fredg]

Any chance you would also know how to calculate the number of items linked to
one contact?

I have a table with a Colum labeled "date arrived" and "date returned." I
have an empty colum with "Number of Days." How do I make number of days
automatically populated useing the other two fields.

Everytime I try and add a formula to the cells I get an error: "The Value
you entered isn't valid for this field.

Thank you.

Not only you should not do this in a table, you cannot do this in a
table.

Here is one way to get the NumberOfDays difference.
Create a query.
Include the fields you need, but NOT the "number of days" field (that
field should be deleted from the table).
Add a new column:
NumberOfDaysateDiff("d",[DateArrived],[DateReturned])

You can use this new field in any report.

Or you can, in a Form or in a Report, add an unbound control to the
form/report.
Set it's control source to:
=DateDiff("d",[DateArrived],[DateReturned])

Again, this calculated control should not be saved in any table.
Whenever you need the data, re-calculate it.

In an unbound control:
=DCount("*","TableName","[SomeField] = SomeCriteria")

You'll need to do some homework to make the above work.
Look Up DCount in VBA Help.
Also Where Clause + Restrict data to a subset of records"