Compose If statement with fields from recordset (1 Viewer)

[Harris@Z]

Hi,

I wonder if anyone can help please.

I have the following statement:
If rsAct("InvoiceLine_ItemRef_FullName") <> "Discount" _
And rsAct("InvoiceLine_ItemRef_FullName") <> "URGENT" _
And rsAct("InvoiceLine_ItemRef_FullName") <> "Courier" Then

Debug.Print 'Working'"
End If

However, since the 'Discount', 'Urgent' and 'Courier' may vary from one situation to another, I would like to have these values pulled from a Table and create a dynamic IF-THEN statement that can be evaluated.

In other words, I have composed the following:
QBStmt = " rsAct(" & Chr(34) & "InvoiceLine_ItemRef_FullName" & Chr(34) & ") <> '" & rsQ("Response_For") & "' AND "
QBStmt = Left(QBStmt, Len(QBStmt) - 4)
QBStmt = "If " & QBStmt & " Then Debug.Print 'Working'"
Eval (QBStmt)

However, this does not work.
I do not know if one can create and IF-THEN statement pulling in recordset fields.
Can anyone assist?

Harris

 

[theDBguy]

Hi. I'm not sure I understand your question. Can you describe the problem some more? Thanks.

 

[jdraw]

Just saw DbGuy's post while composing -- I agree that more info with context may make your post more clear. You could try using = rather than <> and adjust logic accordingly.

 

[Harris@Z]

Thanks for your quick responses.
I hope that this is clearer.
I have an IF statement that checks to see whether a field value in a record is NOT = 'A' and NOT = "B" and NOT = "C", and if true, will update another field. At present the values "A", "B" and "C" are coded into the IF statement.

I want to instead have the values "A", "B" and "C" in a table, so that if necessary, I can either change say "B" to "H", or add "J" and "K" and then construct dynamically an IF-Then statement instead of having to change the coding.

In other words, instead of having to write the code . . . . AND rsAct("Field") NOT = "B" AND rsAct("Field") NOT = "C" . . . ., to have it dynamically created, where the "B" and "C" would have been 'pulled/inserted' from a table. I could use code to write " rsAct("Field") <> '" & rsQ("Field") AND " and do a loop through the rsQ recordset to input A, B, C, etc, but then the IF-Then does not work.

Is that a bit clearer? Hope so!

 

[jdraw]

See this recent post

Checking Fields

I have a form that check for a condition on several fields. I'm using the following... If [field1] "*H*" then … (this works) if [field1] "*H*" And [field2] "*H*" then... (does not work when I put two together) I want to be able to check four fields at once. Not sure how to add them...

www.access-programmers.co.uk

 

[Harris@Z]

Thanks for the link. My question is not based on the logic, but how does one construct a dynamic IF-Then statement with a variable number of AND variables, and that will execute correctly when constructed.

 

[jdraw]

The recordset will have a Fields collection numbered from 0 to fields.count-1

You could use a for loop

for i = 0 to rst.fields.count-1
debug.print rst.fields(i).name
next i

 

[Micron]

I doubt you can construct as string and use it as normally complied code. I think you would have to create variables for the component parts and if this is about some parts not being required, then you'll have to apply a test to determine whether or not to include them. So something like

Dim str1 as string, str2 as string, etc.
str1= Nz(rs("field"),"")
str2 = Nz(rs.("field2"),"")
If Not str1="" Then 'update accordingly
If Not str1 = "" and str2 = "" then 'update accordingly

or use Select Case block(s)

 

[theDBguy]

Thanks for your quick responses.
I hope that this is clearer.
I have an IF statement that checks to see whether a field value in a record is NOT = 'A' and NOT = "B" and NOT = "C", and if true, will update another field. At present the values "A", "B" and "C" are coded into the IF statement.

I want to instead have the values "A", "B" and "C" in a table, so that if necessary, I can either change say "B" to "H", or add "J" and "K" and then construct dynamically an IF-Then statement instead of having to change the coding.

In other words, instead of having to write the code . . . . AND rsAct("Field") NOT = "B" AND rsAct("Field") NOT = "C" . . . ., to have it dynamically created, where the "B" and "C" would have been 'pulled/inserted' from a table. I could use code to write " rsAct("Field") <> '" & rsQ("Field") AND " and do a loop through the rsQ recordset to input A, B, C, etc, but then the IF-Then does not work.

Is that a bit clearer? Hope so!

Hi Harris. Thanks for trying to clarify. I think I understand what you mean now. Unfortunately, Eval() can only work with some simple expressions. When you need a complex structure like and If statement, I am not sure Eval() can work. The closest thing you can do is split the code into two parts: the logic and the data. If your logic structure is set and only the data changes, then just simply code the logic using variables, rather than try to create a String to represent the code and then try to execute it. The way I can see that working is if you write a function on-the-fly, which means you'll need additional reference in your project, and you can't distribute a ACCDE (compiled) version of your db.

Just my 2 cents...

 

[Cronk]

I can't see Eval() handling debug.print

Does

if rsAct("InvoiceLine_ItemRef_FullName" ) <>  chr(34) & rsQ("Response_For") & chr(34) then

do what you want?

 

[Harris@Z]

Thanks for all the input, much appreciated.

I will test by changing debug.print to something like A=2 and testing, but suspect that theDBguy's advice is probably most appropriate.

I appreciate that one can have this work using very complex methods but I do not want to go there!

I was hoping that there was a simple solution that someone knows about and that I as a basic programmer is unaware of.

 

[theDBguy]

Thanks for all the input, much appreciated.

I will test by changing debug.print to something like A=2 and testing, but suspect that theDBguy's advice is probably most appropriate.

I appreciate that one can have this work using very complex methods but I do not want to go there!

I was hoping that there was a simple solution that someone knows about and that I as a basic programmer is unaware of.

Hi. What you're asking for, I think, is a VBA interpreter, which, if it was available, would probably be a third party add in. However, I doubt anybody would build it because there won't be any market for it since Access already has a built-in interpreter.

 

[jdraw]

Perhaps theDBguy understands your post and has given a good summary of what you are asking and the likely outcome.
I'm still unclear of what you are asking. I first thought your issue was iterating fields without knowing the individual names -but that got no traction. It's still possible that you're talking about a function where you can have symbolic parameters to represent real values and substitute your actual values when calling/executing the function.
My guess is we still haven't seen a clear example of the requirement in context or terms we understand. But maybe it's just me who doesn't understand.

 

[theDBguy]

Perhaps theDBguy understands your post and has given a good summary of what you are asking and the likely outcome.
I'm still unclear of what you are asking. I first thought your issue was iterating fields without knowing the individual names -but that got no traction. It's still possible that you're talking about a function where you can have symbolic parameters to represent real values and substitute your actual values when calling/executing the function.
My guess is we still haven't seen a clear example of the requirement in context or terms we understand. But maybe it's just me who doesn't understand.

Just to clarify, here's what I see the OP is trying to do:

Dim strCodeToRun As String
strCodeToRun = "If rst1('Field1')=rst2('Field2') Then Debug.Print True"
Eval(strCodeToRun)

 

[jdraw]

Thanks. That's clear. It was the in line execute part....

 

[MajP]

Should be doable IMO; however, I often have to question a DB design that would have to do it

Public Function IsIn(ValueToCheck As Variant, ParamArray Values() As Variant) As Boolean
   Dim I As Integer
   For I = 0 To UBound(Values)
    If ValueToCheck = Values(I) Then
      IsIn = True
      Exit Function
    End If
   Next I
End Function
Public Function IsNotIn(ValueToCheck As Variant, ParamArray Values() As Variant) As Boolean
  Dim I As Integer
  IsNotIn = True
  For I = 0 To UBound(Values)
    If ValueToCheck = Values(I) Then
      IsNotIn = False
      Exit Function
    End If
  Next I
End Function

Public Sub TestIn()
  Dim x As String
  x = "A"
  Debug.Print IsIn("A", "B", "C", "D", "E")
  Debug.Print IsNotIn("A", "B", "C", "D", "E")
  Debug.Print IsIn("A", "A", "B", "C", "D", "E")
  Debug.Print IsNotIn("A", "A", "B", "C", "D", "E")
End Sub

 

[MajP]

Also you could do it leveraging SQL

Public function IsNotIn (ValueToCheck) as boolean
  dim strSql as string
  dim rs as dao.recordset
  strSql = "Select * from someTabe WHERE " & value To check & " IN (Select SomeValues from SomeTable)"
  set RS = currentDb.openrecordset(strSql)
  if rs.eof then IsNotIn = TRUE
end if

Looks to me that you are doing a simple Not In query and over complicating this. https://www.w3schools.com/Sql/sql_in.asp

 

[Micron]

Dim strCodeToRun As String strCodeToRun = "If rst1('Field1')=rst2('Field2') Then Debug.Print True" Eval(strCodeToRun)

That was my interpretation too, which is why I said I doubt it can be done. The only way I know of to create code in Access is to key it in. What seems to be wanted is a sort of command line interpreter so that if I build a string, Access will execute it as code. It would be like building a sql statement in vba and passing it so that the JET/ACE portion of Access does something with it. Passing a simple string to Eval should result in an error (which wasn't stated as far as I can see).

 

[Pat Hartman]

I don't see where you've defined the circumstances that control the variables. Depending on this, you might be able to create a table with a set of three values for each condition. Don't forget, if you are using a recordset, you must open it and position it to the record you want. If you properly tablize this and can determine the "circumstance" in your query, you might even be able to join to the lookup table.