vba if ...then conditionals

[sjsean]

I am hoping this is possible, but I haven't found much information
searching the web or the groups. I have a table of rules which I want
to use for examining a second table of actual data and then make
appropriate updates.

I pull both sets into a recordset and then run the rules recordset
record against the data recordset records to determine matching
situations and if a match occurs update that record with further
details also contained in the rules table/recordset.

The problem I am running into is the only way my If ... Then
statements work is if I hard code the conditional of Like, =, Not
like, etc. I can hard code this, but I was hoping there was a way to
pull the operand into the statement from the rules table and do the
whole thing that way. If I do try the statement via the rules table I
get an error of type 13 and type mismatch.

Is there a way to do this efficiently? My rules table has the
conditional field set as a text field which may not be right, but then
what to do when the value is like or not like or left or right.

rules table:

field1 = acct_number
comparison1 = "="
value1 = "3"
businessid = 2000

essentially I want: If acct_number = "3" then do something


Thanks!

Sean

 

[BruceM]

Maybe somebody else will understand in enough detail to offer a suggestion,
but I suspect that without more information it may not be possible to
suggest anything specific. An example or two would probably help.

From what I can tell you are comparing Field1 to a value that is part of the
same record, but I cannot see the significance of the "3".

 

[ghetto_banjo]

What he is trying to do, is pull the Operator for the conditional
if...then statement.

in his "rules" table, he has a field which stores things like =, <, >,
Like, <=, etc.

He wants to be able to pull that field and dynamically change his
if..then statement to use the correct operator.


I personally have never seen that done.

 

[John Spencer]

Take a look at the EVAL function. It M I G H T help you. You will have
to play around a lot to make it work.

Dim strEval as String

strEval = Field1 & " " & Comparison1 & " " & Value1

If Eval(strEval) = True Then
'Do Something
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[BruceM]

I kind of got that. I haven't seen it either. Without knowing something
about how the database is put together and how the rules apply I don't see a
way to suggest anything. It may well be that there is another way to
approach this, but I don't see how to use a stored operator. Bet I can
think of is some kind of If or Select Case that uses different expressions
based on the value of the "Operator" field:

Select Case Me.Operator
Case "+"
Me.SomeTextBox = Me.Field1 + Me.Field2
Case "-"
Me.SomeTextBox = Me.Field1 - Me.Field2
End Select

I doubt there is a way to avoid a lot of hard coding, but I couldn't say for
sure.

 

[BruceM]

I was trying to find a way to do just that. Val wasn't doing it. Glad you
pointed it out. Never know when it may come in handy.

I expect there's no getting away from quite a bit of hard coding.

 

[ghetto_banjo]

yeah looks like Eval might be useful here, but that Select Case is a
great idea as well. if he only uses a couple different operators
there, that would be a fairly efficient way to program that.

nice thinking there bruce.