Complex Excel Formula Conversion (1 Viewer)

all4jcvette · Sep 30, 2011

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")))

vbaInet · Sep 30, 2011

Have you thought about a SELECT CASE statement or you're just not sure how to interpret the code?

all4jcvette · Sep 30, 2011

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?

all4jcvette · Sep 30, 2011

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?

vbaInet · Sep 30, 2011

You are not going to be inserting the value into a field. That's bad practice. The rule is, if you can calculate it then don't save it.

SELECT CASE -->
http://www.techonthenet.com/access/functions/advanced/case.php

all4jcvette · Sep 30, 2011

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.

vbaInet · Sep 30, 2011

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.

all4jcvette · Sep 30, 2011

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.

vbaInet · Sep 30, 2011

That's a slower alternative but at least you did it! So, good job

all4jcvette · Sep 30, 2011

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.

vbaInet · Sep 30, 2011

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

all4jcvette · Sep 30, 2011

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.

Brianwarnock · Sep 30, 2011

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

vbaInet · Oct 1, 2011

Brianwarnock said:
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])

Complex Excel Formula Conversion (1 Viewer)

all4jcvette

Registered User.

vbaInet

AWF VIP

all4jcvette

Registered User.

all4jcvette

Registered User.

vbaInet

AWF VIP

all4jcvette

Registered User.

vbaInet

AWF VIP

all4jcvette

Registered User.

vbaInet

AWF VIP

all4jcvette

Registered User.

vbaInet

AWF VIP

all4jcvette

Registered User.

Brianwarnock

Retired

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread