Use a Cell to Set the Condition of an IF Statement in VBA

[Tracy B]

Hi!

I want to create a dynamic IF statement linked to a table. The end purpose is to determine a customer's eligibility for a certain promotion. Since our programs and the qualifications for them are ever-changing, I want to be able to set up a table that the end-user could edit without getting scared off by VBA.

So, the users will bring up a customer's information on a form, and the eligibility will depend on a few different factors (i.e. state they live in, term of subscription, relationship name, etc.). I can work out all the complicated stuff later...All I need to have is a way to take a condition ex. (STATE = "NJ"), which would be written in a cell in my CONDITIONS table, and have it actually evaluate that in the code on my form.

So far I have the following:

Private Sub EvaluateEligibility_Click()

CurrentRow = 1

Makeitstop = False

Do While Makeitstop = False

If Eval("Forms.CustomersForm." & DLookup("[Criteria1]", "Conditions", "[ID] = " & CurrentRow)) = True Then
    If DLookup("[Eligible]", "Conditions", "[ID] = " & CurrentRow) <> "No" Then
        MsgBox DLookup("[Eligible]", "Conditions", "[ID] = " & CurrentRow)
        Makeitstop = True
    End If
End If

CurrentRow = CurrentRow + 1

Loop

End Sub

The Conditions Table has such things as:

ID | Criteria1 | Eligible
1 | State="NJ" | No
2 | State="VT" | Yes

And it should theoretically cycle through each ID until, using the data inputted on the form, both the Criteria1 = True and Eligible <> "No".

If I just do

MsgBox Eval("Forms.CustomersForm." & DLookup("[Criteria1]", "Conditions", "[ID] = " & CurrentRow)) = True

it returns the correct True or False value based on how State is filled out on the form vs. the criteria in the Conditions table, but if I try to use it in an IF statement, it throws
"Run-time error '2431': The expression you entered contains invalid syntax"

Can anyone help me fix this or suggest a better way to do this?

Thanks!!

[sierra7]

Hi Tracy
Personally I avoid programming and especially loops within loops which do my head in! I suggest you can probably do this with calculated controls.

It seems that some of your criteria are to be found on your form. You may not have 'Term of Subscription' but you could create an unbound field (call it txtTerm) and set its Control Source

= DateDiff("m", [txtStartDate], Date())

where txtStartDate is the control that displays their start date. The 'm' indicates you want the number of months between the two dates, 'y' should give years.

For your States criteria you will need a table, say tblStates, with one field for USPS and a Yes/No field, lets call it 'bElig'. (Gor blimey!(or Gee wizz! for you guys) I've just looked at your sample data. More in a minute). You can either add all 58 US postcodes or just the states you ship to, then give your users a form to update the check-box to say whether a particular state is Eligible.

You can then add another unbound control on your form (this one can be set Visible = No), call it txtPost if you like, and set its Control source

=DLookup("[USPS]", "tblStates", "[bElig]= True and [USPS]= [txtStateCode]")

where txtStateCode is the control on your form that holds the State. This control txtPost will be blank if the current state is not eligible but hold the US Postal Service code if it is found on the 'eligible' list.

You then need yet another unbound field, this one with big red letters and the control source

=IIf([txtTerm]>5,IIf([txtPost]<>"","Eligible",""),"")

This just does two tests; that the Term is greater than 5 and that the State is checked Yes in tblStates. The 5 is hard coded in at present but this could be another control on the form, populated by another lookup or by the main query.

The only tricky bit is the nested IIF() statements. But they are not that tricky and you can easliy add more criteria.

I noticed in your table Conditions you had a Yes/No/Maybe. I don't know how you would handle a Maybe, so its a NoNo!

Also you had a field called "Criteria" with an entry such as 'State = "NJ"' This would give trouble because the quote signs may be interpreted. It's also unneccesary to hold "State =" in the database.

If you don't like the idea of 'calculated controls' and you feel the logic of the process is hidden, then you can leave their Control Source blank and set their content in the Form's On_Current event. e.g.

Me.txtTerm = DateDiff("m", [txtStartDate], Date())
Me.txtPost =DLookup("[USPS]", "tblStates", "[bElig]= True and [USPS]='" & Me.txtStateCode & "')")
Me.txtEligible = IIf([txtTerm]>5,IIf([txtPost]<>"","Eligible",""),"")

Get the idea? There is a slight difference in syntax when the code is written as statements rather than embedded in the control.

I'll try and re-work your database and return it.

S7

[Tracy B]

Thanks SO MUCH, but...

Hi Sierra,

Thank you so much for your incredibly detailed and though-out response. I really do appreciate it!

Unfortunately, I should have been more clear that the problem is more complicated than this example.

I’m can’t go into details because of security issues, but consider these examples:

What if a customer is only eligible in Nevada if their subscription term is 36, 60 months from their start date, or today’s date (in case they extend the subscription period by updating the termination date)?

What if a customer can absolutely not be eligible if their first name is John, Paul, George, or Ringo?

What if a customer could be eligible if their hair was brown, their mother’s name was Mindy, and they didn’t live in one of the 48 continental states, but they’d only be eligible to get a certain type of magazine?

Most importantly, what if I leave these folks tomorrow, and another promotion comes along? Are they going to be able to look at what I’ve done and understand it?

I have “Maybe” in there, because some of the promotions’ eligibility is subject to approval. There are no guidelines that we know of; we just do it if we’re told to do it. So, if a relationship name is Apple, it should say “Client,” because approval must come from our client Mr. Apple.

I’d need to be able to set up nested if statements, dependent on the qualifications of each program. Sometimes a single program can have 10 criteria, all of which would have to be checked against the data on the form and return an answer instantaneously .

The nature of the comparisons would need to be completely dynamic. Maybe tomorrow they’ll only be accepting redheads named Nancy in Omaha with street numbers greater than 1,000. If I have all of the equations hard-coded, but the info in tables, they would need to find all of my comparisons and change them.

That’s why I’d like to set it up this way. All they have to write is the equation (State = “NJ”, Hair Color = “Brown”, StreetNumber > 1000, FirstName NOT LIKE “M*”), and VBA should pick that up and pretend its code. (I write ‘If Dlookup(“Criter ia”,”Conditions ”,”ID=1”)=True’ . VBA looks at that cell which reads ‘State = “NJ”’ and acts as if its code originally said ‘If State = “NJ”’).

I thought a VBA loop would be the most elegant way to solve that, but I can't understand why I'm getting that darn error message!

Thanks again!!

[sierra7]

OK,
I'm starting to get the picture but I dont think you can do it like that.

To start with, how does the user know it is 'Hair Color' not 'Haircolor' and 'FirstName' not 'First Name'? Fieldnames with spaces must be enclosed in square brackets. Also, you would need 'and' between your criteria, not commas.

If you want total flexibility I think you need a form where all of the potentially filterable fields are listed and then permissible values entered. From those entries you can formulate your filter. You would then only display Eligible customers.

There used to be a feature in Access called Filter By Form. I've never used it but it might be worth checking out in this scenario.

Best of luck
S7

PS Sierra Seven was my radio call-sign when I was upcountry (Congo ,Iraq etc) I did not know it was a girls name until I saw Sierra Boggess sing in Phantom recently. She's gorgeous, I'm not. Please dont call me Sierra.

[NeoPa]

@Sierra7
Interesting indeed. Sierra being the phonetic alphabet version of S. I think I'd better remember that in future too, as I was somewhat confused (although I've noticed a very patient attitude from your posts that may have gone with a member of the fairer sex, I was loath to make assumptions). Oh, and the sig is not an abbreviation, but simply another way of writing Sierra7? Cool.

On to the question and a temporary end to my blathering.

The first thing that springs to mind is that such an endeavour does rather indicate some pretty advanced logic. Not for the faint-hearted I would say. It would require a design and a build that, together, could be used to process the data in the way required. Quite difficult I would say. Something that comes to mind is the Evaluate() function, but that's an Excel Application object method. Could still be used with a reference set, but you'd still need to give more careful consideration to exactly what you wanted implementing first. Or maybe it wouldn't help that much. I'd need to do it before deciding. I don't propose to, but even that would require a better and clearer understanding of your exact aims.

S7 makes a good point about what you allow the user to enter into these fields (Not cells in Access). I suspect each possible entry should be controlled by ComboBoxes with the available data populated in them.

My suggestion is that the first thing to do is to consider how you will store the logic data. Then, and only then, will it make sense to try to implement an engine to process and interpret that logic.

[sierra7]

Tracy
Here is a link to Query by Form (QBF) that might help

http://support.microsoft.com/kb/304428

S7

[NeoPa]

Strangely, there was a similar thread on here recently which also got me thinking of the Evaluate() method in Excel, but then I found that Access has a Run() method that works in a similar way. I haven't tried QBF myself, but it's probably worth looking into first, before getting complicated and making code produce the code.