Access form calculation

[MattyL]

Hi



I've been reasonably clever and, on a form, created a text box that calculates the result of subtracting data (a date) in one text box from another date in a second text box (using DateDiff).

The correct answer is showing in the form.

However, I want to run a query or a report that is based on this automatically generated number, but whenever I view a table, query or report the 'answer' does not appear in the field. Therefore I can't run a query based on this value.

Any advice?

[Robeen]

Can you explain with a little more detail exactly what you need to do once you have your 'answer' on the Form?

[MattyL]

Hi

Thanks for responding so quickly.

The form contains the following fields...

1. Date work order placed (e.g. 1st November 2011)
2. No. of days elapsed since order placed (this is worked out using DateDiff, i.e. subtracting today's date from date order placed, to give a value), using the e.g. of 1st November 2011 the value returned would be 7, based on today being 8th November 2011)

I want to run a query or a report which allows me to view, say, all orders placed more than 7 days ago. I can do this on a query by inputting a criteria of < (a specific date) in the 'date of order' field, but this is not ideal. I'd rather put a value in the 'no. of days elapsed' field, e.g. to show all works orders which are over 7 days old (>7), and then see this info on a report.

Am I making myself clear enough?

Thanks

[Robeen]

Try this in the Criteria for the date field in your query.

DateDiff("d",[DateField],Date())>7

[MattyL]

Yep, that appears to work. Thank you very much for your help.

Shame though that Access doesn't allow for the calculation to be done within the table.

All the best

[Robeen]

You CAN do calculations in your Access Tables - using the same logic.
Create a field and put the calculation into the field.

BUT, it is better to do calculations like the one you need in a Query.
The value in the calculated field [if you decide to have one] will change every day - so it is not a REAL value.
Since it can be calculated at any time using the query, it is better not to store it in the table.
The query will make the value available to you whenever you need - and this is just a more efficient way of doing things.

All the best!!

[MattyL]

Thanks for your help - I got it working.

However, I've now moved the goalposts a little.

My new form has three input fields, and one automatically generated field which has used the datediff expression:

1. Machine name
2. Date fault reported
3. Date fixed
4. Days out of service (field 3 minus field 2)

What I now want to do is run a report or query in which I select a particular machine to see how many times, and for how many days, it has been out of service. I know I could do this really easily from Excel using a pivot table, but my database is doing other stuff too and I would like to keep using it.

So the report would look something like this...

Machine No. Date fault reported Date fixed Days out of service
1 10/10/11 12/10/11 2
1 15/10/11 18/10/11 3

Total days out of service 5

Can you help?

[Robeen]

If you want to just search for information on ONE Machine at a time, you can create what is known as a parameter query and what that will do is prompt you for a machine # each time you run the query.

I just created a real simple query with 3 fields [including a field with a name in it.
In the criteria of the Name field, I put this:
[Name?]

When I ran the Query, it prompted me for a Name for that field and when I provided the Name, the query only returned the row that had a matching value in that field.

You could put something like [Machine# ?] in the Criteria for Machine Number in your query.

I hope this helps!

[Robeen]

P.S. You will have to add the 'Days_Out' field and do the DateDiff calculation in there.

It should look like something like this in the Query field that you create:

 Days Out Of Service: DateDiff("d", [DateFaultReported],[DateFixed])