Calculation field

[johnny]

Hello all,

Sorry for the noob question but it is my first (and probably the last) time with Access so I ask for your understanding.

Here it is the problem.

I have one table:
ID | Who | From | To | Expected | Points

where,
From, To, Expected – Date/Time
Points – Number

In "Points" field I want to have numbers which should be done by equation: "Expected – (To – From)" only when there is "To" declared. It would be a column with minus points for people who return books later than they declare. But I have no idea how to achieve this requirement in Access.

Please help, how to do it if it’s possible at all.

I use MS Access 2007


Best,
Johnny

[June7]

Cannot do calculations in table in Access 2007 (this is new feature in 2010). Do this calculation in a query.

[rpeare]

As june said, don't do this on a table (and you can't with the version you're using) but I don't understand your query.

Are your points based on how many days after their expected return they actually returned the item? And are you expecting a numeric value because you're operating on date fields.

For instance let's say your FROM date was 6/1/2011 and your TO date was 6/15/2011. If you subtracted these (datediff function) you'd get 14 days. Now let's say your expected date was 6/16/2011, if you perform another subtraction (dateadd function using a -14) you'd get 6/2/2011 so I'm not sure how you would reflect that as points.

[johnny]

@rpeare, you're right. Is there any function in Access that make subtraction possible for Date/Time fields?

Or I could use Expected as a Number, but then it would be a substraction Number - Date/Time. Is it possible at all?

E.g:
ID - AutoNumber
Who - Text
From - Date/Time
To - Date/Time
Days - Number
I would like 'Points' from Query to be Numbers

ID| Who | From | To | Days

1 | John Malkovic | 2011-07-25 | 2011-08-01 | 5
2 | Mathew Blanc | 2011-07-18 | 2011-07-25 | 7
3 | Anna Geller | 2011-08-07 | | 9

Points:
1) 5 - (01-08-2011 - 2011-07-25) = 5 - 7 = -2
2) 7 - (2011-07-25 - 2011-07-18) = 7 - 7 = 0
3) There is no "To" so there is also no Points

[rpeare]

the days field in your example can be calculated by the datediff function

datediff("d", firstdate, seconddate)

in your case it'd be

datediff("d", from, to) or datediff("d", to, from) whichever gives you the result you want.