Number of days issue

[posfog]

Hi there
I wondered if some could help me as new to access and having the following issue

I have a database where there are two dates that need to bee filled in

1 is called open for when a issue is reported and the 2nd on is called closed for when the issue has been resloved.

Now i also have a field called days open where i want to beable to show the number of days a issue was open for ( number of days between open and closed) or if the issue is still open for it to work out the number of days from it being open and the current date.

Is this possible at all?

Regards
P

 

[Joe4]

Ok, I will ask again, can you please create the query I asked above, and post the SQL code here?
Simply create a new query from your table and add the two date fields. That is it.
Then change the query to SQL View, and copy and paste the SQL code here.

 

[posfog]

You will have to forgive me as i am a novice with Access at the moment,

Let me see what i can do with the query

 

[posfog]

Ok, I will ask again, can you please create the query I asked above, and post the SQL code here?
Simply create a new query from your table and add the two date fields. That is it.
Then change the query to SQL View, and copy and paste the SQL code here.

Is this what your talking about?

SELECT Sheet2.[Open], Sheet2.[Closed]
FROM Sheet2;

 

[Joe4]

Yes, exactly.

So I created the Table, exactly the same way on my side, and created the query.
And here is the SQL Code for the working query:

SELECT Sheet2.Open, Sheet2.Closed, IIf([Closed]>0,[Closed]-[Open],Date()-[open]) AS Days_Open
FROM Sheet2;

So, if you do go into the query you created for this, change to SQL View, and copy and paste the code above there, does that work when you go to view the results?

 

[posfog]

YES!
So how do i get that into my table?

 

[Joe4]

You don't need it in your Table. What makes you think you need it there?
All calculations should be done at the Query level, not the Table level.
Just about anything that you use Tables for (i.e. Forms, Reports, Exports) you can also use a Query for.

 

[posfog]

Hi Joe
The idea behind it being in the table is when a user is looking at one record via the form I have created I would like them to be able to see how many days the record has been open for prior to it being closed

Hope that makes sense and also thanks for all your help so far

 

[Joe4]

Base the Form on the Query, not the Table, and the calculated field will be there.

 

[posfog]

How can I do that?
I am currently using the form as a front end to enter the data

 

[Joe4]

Create your query to show all the fields in your data, as well as any calculated fields that you want.
Then, go into your Form, click on Properties, and change Record Source property from the Table to the Query you just created.
It will then show you all the fields in your query that are Available, and you can add the Calculated fields to the Form.