Complex Excel Formula Conversion

all4jcvette

Registered User.
Local time
Yesterday, 19:29
Joined
Jun 12, 2009
Messages
10
I have a complex if/and/or calculation formula in Excel that I want to convert into a query for access. The field names in Excel and Access are the same "effective_date" and "inactive_date". The new column is named "status_type". I've tried all kinds of conversion, and I just can't figure it out. Any help would be appreciated.

Excel Formula:
=IF((AND([effective_date]="",[inactive_date]="")),"Pending Approval",IF((AND([effective_date]<=TODAY(),OR([inactive_date]>=TODAY(),[inactive_date]=""))),"Active Enforcement",IF((AND([effective_date]>=TODAY(),[inactive_date]>="")),"Future Enforcement","Inactive")))
 
Have you thought about a SELECT CASE statement or you're just not sure how to interpret the code?
 
I'm not sure how to write a select case statement in access. Would that let me do the compare between the two field and let me insert a value based on the results?
 
I'm really close.
status_id: IIf(([effective_date]=Null And [inactive_date]=Null),"Pending Approval",IIf(([effective_date]<=Date() And [inactive_date]>=Date()),"Active Enforcement",IIf(([effective_date]<=Date() And [inactive_date]=Null),"Active Enforcement",IIf(([effective_date]>=Date()),"Future Enforcement","Inactive"))))

The Pending Enforcment and the second Active Enforcement don't work yet, they both return Inactive. Any thoughts?
 
I'm not inserting it into the table, just presenting the value through a query based on a two field calculation. I don't get how the case statement works in a query to do what I'm trying to do with determining a values base on a number of combinations on two field. Sorry, just a beginner with this stuff.
 
If you write the Select Case statement I will tell you how to integrate it with your query ;)

Just give it a try and let me see what you came up with.
 
I got the IF statement working as expected.

status_id: IIf(([effective_date] Is Null And [inactive_date] Is Null),"Pending Approval",IIf(([effective_date]<=Date() And [inactive_date]>=Date()),"Active Enforcement",IIf(([effective_date]<=Date() And [inactive_date] Is Null),"Active Enforcement",IIf(([effective_date]>=Date()) And [inactive_date] Is Null,"Future Enforcement","Inactive"))))

Took me a few hours, of messing with it, but it works as expected.
 
That's a slower alternative but at least you did it! So, good job :)
 
I'd do a select case statement, I just don't understand the logic when doing a compare across two columns. All of the examples I've seen are based on a single column, with small results, as you can see this is a little more complex then that. With no reference, I'm not sure how to do it. I'd love a faster solution, i just lack the knowledge of how to start it.
 
I posted a link in post #5, maybe you can't see it because you haven't crossed the 10 posts threshold.

Google: techonthenet select case access
 
Thanks, yes I saw your link. However, I just don't understand the logic when doing a compare across two columns. All of the examples I've seen, including in the link, are based on a single column. I don't understand how to go from one column to a multiple column compare with a case statement. Once I see an example, I'm fairly good at figuring out how to continue with it.
 
You would use nested Select Case statements, however I think that I would probably use block form of If .. Then in this case, no pun intended.
It is easier to follow and amend than the nested IIf, provided that you are happy with functions.

Brian
 
You would use nested Select Case statements, however I think that I would probably use block form of If .. Then in this case, no pun intended.
It is easier to follow and amend than the nested IIf, provided that you are happy with functions.

Brian
You're right there Brian. I wasn't paying much attention ;)

Here's the function:
Code:
Option Compare Database
Option Explicit


Public Function GetStatusID(varEffectiveDate, varInactiveDate) As String

    If Len([effective_date] & vbNullString & [inactive_date]) = 0 Then
        GetStatusID = "Pending Approval"
        
    ElseIf [effective_date] <= Date And ([inactive_date] >= Date Or Len([inactive_date] & vbNullString) = 0) Then
        GetStatusID = "Active Enforcement"
        
    ElseIf [effective_date] >= Date And Len([inactive_date] & vbNullString) = 0 Then
        GetStatusID = "Future Enforcement"
        
    Else
        GetStatusID = "Inactive"
        
    End If

End Function
Put this in a global module.

And this is how you use it in your query:
Code:
status_id: GetStatusID([effective_date], [inactive_date])
 

Users who are viewing this thread

Back
Top Bottom