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]

"Days Open" should not be a stored field in your database. The rule of thumb with databases is that anything that can be calculated should not be stored, as that undermines the data integrity and dynamic ability of a relational database. You would just perform that simple calculation in a query using a calculated field that looks something like this:

Days_Open: IIf([Closed]>0,[Closed]-[Open],Date()-[Open])

 

[posfog]

Hi Joe,
I am getting the following error?
The expression you entered had invalid . (dot) or ! operator or invalid parentheses.

Any idea??

 

[Joe4]

You are entering this in a calculated field in a query, right?
Please post the exact formula that you entered here (including all your modifications).

Also, create a new query from the table that contains these fields, add the two dates to the query, change the query to SQL View and copy and paste the SQL code here.

 

[posfog]

Did a new calculated field and entered
Days_Open: IIf([Closed]>0,[Closed]-[Open],Date()-[Open])

into the expression builder then got that error message

 

[Joe4]

Is the correct table added to the field?
Are "Open" and "Closed" the EXACT names of your fields (if not, you will need to change to suit)?
You don't have a field already named "Days_Open", do you?

 

[posfog]

Data stored in Sheet2
One Field called Open
One Field called Closed

No fields called Days_Open

Clicking on More Fields and selecting Calculated field and tried using both Date option and Number and keep getting same issue?

 

[Joe4]

Should have probably asked first, but can you confirm that the two date fields actually use the Date/Time data type in your Table (and are not, in fact, Text)?

Can you do the last step I asked in post 4 and post the code here?

create a new query from the table that contains these fields, add the two dates to the query, change the query to SQL View and copy and paste the SQL code here.

 

[posfog]

The fields are extactly the same and i have no field called Days_open

I click on More fields and chose Calculated field and then entered your formula into the expression builder then got that error message?

 

[posfog]

Both open and closed are date/time fields