Solved Multiple If Expression (1 Viewer)

[Gasman]

Mike,
O/P swapped the last two options, so not Ranman's logic, only part of it, as alluded to by Plog in post #7

 

[Josef P.]

The problem is the return data type of AddWorkingDays.

=> Force to convert date with Datediff:

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;

 

[Mike Krailo]

I just tested Ranman's function in your Query1 form. Probably should name it something better. It worked fine. The function is in the form module so the function won't work without the form.

 

[Teri Bridges]

I'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.

Sorry, there were several responses to My question. I was trying them all at different points. I am down to trying to work with two

#1 If I use the expression builder in the Query 1 I cannot get the Overdue to post correctly
Expr1: Switch([CompleteDate] Is Not Null,"Complete",[ReviewDate] Is Null,"Not Started",[DueDate]>Date(),"Inprocess",[DueDate]<=Date(),"OverDue",True,"???")

#2 If I add "getResults" to my module I cannot get the overdue date to post correctly
Public Function getResult(pvCompDte, pvRevDte, pvDueDte)
Select Case True
Case Not IsNull(pvCompDte)
getResult = "Complete"

Case IsNull(pvRevDte)
getResult = "Not Started"

Case pvDueDte > Date
getResult = "Inprocess"

Case Else
getResult = "OverDue"
End Select
End Function

The Attached Db is with the VBS in the module

 

[Teri Bridges]

The Reply from Josef P. Seemed to work. Now I need to compare to what I was doing and learn. Thank you all for your help.

 

[Teri Bridges]

Here is the resolution Josef P. Was able to provide. My issue, if I understand correctly is that I have a module that uses DiffDays to populate the Due Date field. I needed to connect that information in my query, which I had not done.

 

[Mike Krailo]

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.

 

[Teri Bridges]

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.

Can't I build a table based on the Query and then it will store the data?

 

[Mike Krailo]

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.

 

[Teri Bridges]

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.

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.

 

[Mike Krailo]

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.

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.

 

[Teri Bridges]

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.

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.

 

[Mike Krailo]

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.

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.

 

[Teri Bridges]

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.

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.