SELECT
eventstatus_tbl.eventcycleid,
eventstatus_tbl.eventid,
eventstatus_tbl.reviewcycleid,
eventstatus_tbl.assignedtoid,
eventstatus_tbl.reviewdate,
Addworkingdays([reviewdate]) AS DueDate,
eventstatus_tbl.completedate,
Switch(
[completedate] IS NOT NULL, "complete",
[reviewdate] IS NULL, "not started",
Datediff("d", DATE(), duedate) > 0, "inprocess",
Datediff("d", DATE(), duedate) <= 0, "overdue",
true, "???"
) AS Expr,
Diffworkdays([duedate], [completedate]) AS Late,
Calcdaysdue([duedate], [completedate]) AS DueIn,
eventstatus_tbl.percentcomplete
FROM
eventstatus_tbl;
Sorry, there were several responses to My question. I was trying them all at different points. I am down to trying to work with twoI'm still trying to figure out why Ranman's function didn't work for you. You said you got a prompt for parameter getResult, but that doesn't make any sense. That function returns a string based on your three parameters. He gave you the exact usage.
I see from post #5 that you have a field in the table called Status, yet you are now trying to calculate the status in the query. If you are calculating the field in the query, you should not have a bound field called status. You cannot have a bound field called status and then try to alias it to status the way you did in the provided picture.
Can't I build a table based on the Query and then it will store the data?Keep in mind, that won't update your bound field called status though. Unless you have a need to store the status, you can just do away with that field all together.
Yes I need to pull a report based on what is in progress, what has been completed and what is overdue. I thought it would be easier if the status was determined automatically. Is there a better way? The other option was to have the user set the status manually. Again I am fairly new to Access and am still learning.Do you really need to store calculated data? My point was more of a recommendation to get rid of the Status field in your table since it is basically calculated.
But you can simply do the needed calculations in your report just like you did with the continuous form. The problem with storing calculated data in a table is it will only be accurate for that one moment in time assuming you just updated the values. It is a very bad practice. Only store raw data in tables and do the calculations in a query when needed for display in forms or to generate a report. There should be no problem creating a report using your query that shows the status column.Yes I need to pull a report based on what is in progress, what has been completed and what is overdue. I thought it would be easier if the status was determined automatically. Is there a better way? The other option was to have the user set the status manually. Again I am fairly new to Access and am still learning.
So, if I understand... Remove those calculated fields from my Table, leave them in the query, and turn the query with the calculated fields into the report.But you can simply do the needed calculations in your report just like you did with the continuous form. The problem with storing calculated data in a table is it will only be accurate for that one moment in time assuming you just updated the values. It is a very bad practice. Only store raw data in tables and do the calculations in a query when needed for display in forms or to generate a report. There should be no problem creating a report using your query that shows the status column.
I'm saying remove the field called Status from your table. I guess you can remove the other calculated fields as well, but the main point is to not store calculated results into your table. There are a few reasonable cases to use calculated fields in your table (that's different). You wouldn't store the age of your employees in a table, you simply store the birthdate (which never changes), then use the birthdate to calculate the age any time you need to in forms or reports. Hope that makes sense. So your on the right track now.So, if I understand... Remove those calculated fields from my Table, leave them in the query, and turn the query with the calculated fields into the report.
Thank you. I do get the concept. The calculated fields are only needed for the report. So really I will not need to create a table based on the query, Just the report. Thank you.I'm saying remove the field called Status from your table. I guess you can remove the other calculated fields as well, but the main point is to not store calculated results into your table. There are a few reasonable cases to use calculated fields in your table (that's different). You wouldn't store the age of your employees in a table, you simply store the birthdate (which never changes), then use the birthdate to calculate the age any time you need to in forms or reports. Hope that makes sense. So your on the right track now.