Results 1 to 10 of 10
  1. #1
    pablo40 is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Location
    Poland
    Posts
    6

    Post String logical expression converted to formula, resulting in UPDATE in a table.

    Hello everyone,



    I've been struggling with the following problem. Let's say I have 8 columns:
    - fields N1,N2,N3 contain some numbers;
    - fields T1,T2,T3 contain some text;
    - field Condition is the one which holds text which represents logical criteria
    - field Outcome is calculated based on a result of checking if criteria is met, as shown below.

    I've been trying to write the following code in VBA but function Eval does not work for me. Besides I do not know how to handle more complex criteria consisting of more than one condition.
    Any help, tips or guidance would be much appreciated!

    k = 1
    Do While Not rs.EOF
    strCondition = "rs!" & rs!Condition
    If Eval(strCondition) Then
    strsql2 = "UPDATE test SET test.Outcome=1 WHERE ID=" & k
    With CurrentDb 'is run for the first time it would be better to check to see if the
    .QueryDefs.Delete ("UpdateTestTable") ' querydef exists and then delete it
    Set qdfNew = .CreateQueryDef("UpdateTestTable", strsql2) ' createquerydef command line follows
    DoCmd.OpenQuery "UpdateTestTable", acViewNormal
    .Close
    End With
    End If
    rs.MoveNext
    k = k + 1
    Loop




    regards,
    Paweł

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What are you trying to accomplish using the EVAL statement because I don't believe you're using it correctly

    For instance EVAL("1+1") would result in a return value from the function of 2

    With your code you are setting the strCondition equal to the string rs!<first value in the condition field>

    So if your condition value on the first record is "XXX" you rstrcondition would be rs!XXX and of course there's no XXX field.

    If you are trying to evaluate whatever is in the field rs!condition that's something else and would be more like

    Code:
    Dim db As Database
    Dim rs As Recordset
    Dim strConditions
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblTEST")
    
    
    Do While Not rs.EOF
        strconditions = rs!condition
        Debug.print "Condition Field Value = " & strconditions
        Debug.Print "Evaluation of string = " & Eval(strconditionsmet)
        rs.MoveNext
    Loop

  3. #3
    pablo40 is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Location
    Poland
    Posts
    6
    Dear rpeare,

    Many thanks for such light-speed reply. However I think we have two different things on mind.
    Namely what I'm trying to achieve is to have a string formula in field 'Condition' which refers to any field (be it numerical or text). For example in column 'condition' I may have the following string: "N1>12". And that means that any record which has value greater than 12 in columns N1 (which is basically rs!N1>12) should then have value 1 in column Output.

    And that's why my first thought was that creating a string which would combine "rs!" prefix followed by rs!Condition (the latter being N1>12 in our example) would work:
    strMyString="rs!" & rs!Condition= "rs!" & "N1>12"= "rs!N1>12".

    My problem is I don't know how to:
    1. Make that string behave like logical formula.
    2. If I happen to have more complex string condition in column 'Condition', for example N1>12 and T2="Washington" how can I capture columns' prefixes and insert smartly "rs!" prefix? Because using the last example my condition then should be:
    rs!N1>12 AND rs!T2="Washington".


    I realize this is pretty a conundrum which has consumed my mind for quite a while. I hope I will not share Smeagal fate!

    regards,
    Paweł

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I agree with rpeare. That is not correct use of Eval() function. Do this experiment. Enter the following lines in the VBA immediate window:

    x = 1
    y = 2
    ?Eval(x & "+" & y)

    Should see output of 3.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    pablo40 is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Location
    Poland
    Posts
    6
    Thank you. But my issue is slightly different.

    I think I have found answer to my second question, which is how to insert "rs!" prefix at the front of all references to any fields. I think I will make users key in square brackets [] over any field names they will refer to. Then I will use this solution:
    Replace(CStr(rs!condition), "[", "rs!["). Thanks to this user will type in: [N1]>12 and it will be converted to "rs![N1]>12". But this is still a string! How can I use it in logical expression such as IF [string] THEN... END IF?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why is user typing in field names anyway? At most, should be selecting from a combobox list. The [] are needed only if field names include spaces or special characters/punctuation (underscore is exception).

    The point we are making is that the field name can't be part of the value in Condition field. The field reference needs to be treated as a variable but putting it into the value is literal text. You could have ">12" in the field then:

    Eval(rs!N1 & rs!Condition)

    Or if must have the field name in the Condition string, will have to extract the parts.

    Eval(rs.Fields(Left(rs!Condition,2)) & Mid(rs!Condition,3))

    So why not save the parts into separate fields then build the string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by pablo40 View Post
    Thank you. But my issue is slightly different.

    I think I have found answer to my second question, which is how to insert "rs!" prefix at the front of all references to any fields. I think I will make users key in square brackets [] over any field names they will refer to. Then I will use this solution:
    Replace(CStr(rs!condition), "[", "rs!["). Thanks to this user will type in: [N1]>12 and it will be converted to "rs![N1]>12". But this is still a string! How can I use it in logical expression such as IF [string] THEN... END IF?
    rs is the object - not the field.

    the ! is the object class item/element inside the object. Since it is a recordset - you are referring to a field which is <i>inside</i> the object/recordset.

    It's the "bucket" that holds the fields. You shouldn't event be using replace() function on it - It won't work because it isn't stored data only what after the rs! is.

    You want to evaluate the values inside the object - which is the fields.

    Code:
    Replace(CStr(rs!condition), "[", "rs![")  should be replace(valueToReplace,findmatch,replacewith)
    
    essentially the replace looks inside a string - and returns the same string with the new changes
    
    rs!Field = replace(rs!Field,"removeThisFromValue","toThis")
    If you get that part let me know and I see if I can answer the next question.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if this is your original CONDITION

    N1>12

    What you really want to do is replace the value of N1 with another value from your recordset, so let's assume tblTest has a field named N1.

    What you are trying to do is find the parse points of this formula (N1) is what you want to convert then you have an operator (>) then you have a value to check against (12)
    To make this easier for my example I am going to assume you have spaces between the elements i.e. your CONDITION field would be N1 > 12, and further that the field in your recordset is always the first element

    Here's my sample data:

    ID N1 T2 Condition
    1 10
    N1 > 12
    2 14
    N1 > 12
    3
    Alamaba T2 = 'Washington'
    4
    Zaire T2 = 'Washington'
    5
    Washington T2 = 'Washington'

    Here is the code I ran:
    Code:
    Dim db As Database
    Dim rs As Recordset
    Dim strConditions
    Dim sField As String
    Dim fieldvalue
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblTEST")
    
    
    Do While Not rs.EOF
        strConditions = rs!Condition
        sField = Trim(Left(strConditions, InStr(strConditions, " ")))
        fieldvalue = rs.Fields(sField)
        If InStr(strConditions, "'") > 0 Then
            fieldvalue = "'" & fieldvalue & "'"
        End If
        strConditions = fieldvalue & Right(strConditions, Len(strConditions) - InStr(strConditions, " "))
        Debug.Print strConditions
        Debug.Print Eval(strConditions)
        rs.MoveNext
    Loop
    Here are my debug.print results:

    10> 12
    0
    14> 12
    -1
    'Alamaba'= 'Washington'
    0
    'Zaire'= 'Washington'
    0
    'Washington'= 'Washington'
    -1

  9. #9
    pablo40 is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Location
    Poland
    Posts
    6
    Dear all,

    Many thanks for your input. I think I have it!!!!
    Here it comes.

    1. The code:
    Sub DynamicQuerries()


    On Error Resume Next
    DoCmd.SetWarnings False


    Dim db As Database
    Dim rs As Recordset
    Dim strConditions


    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblMultipleKeys")


    k = 1
    Do While Not rs.EOF
    strConditions = Replace(CStr(rs!Condition), "[", "tblMultipleKeys.[") 'I need to consider if I really need REPLACE function
    'my fields names are made up of one word only

    strupdatesql = "UPDATE tblMultipleKeys SET tblMultipleKeys.Outcome=10 WHERE (" & strConditions & " AND tblMultipleKeys.ID=" & k & ")"
    With CurrentDb 'is run for the first time it would be better to check to see if the
    .QueryDefs.Delete ("UpdateTestTable") ' querydef exists and then delete it
    Set qdfNew = .CreateQueryDef("UpdateTestTable", strupdatesql) ' createquerydef command line follows
    DoCmd.OpenQuery "UpdateTestTable", acViewNormal
    .Close
    End With
    k = k + 1
    rs.MoveNext
    Loop


    End Sub

    2. The test results!
    ID Condition Outcome N1 N2 T1 T2
    1 N1>20 0 0 0

    2 N1>20 0 14 0

    3 N1>20 AND N2>100 10 56 101

    4 N1>20 10 34 0 W
    5 N1>20 0 20 0

    6 N1>20 AND T1="W" 10 22 0 W
    7 N1>20 AND N2>100 10 123 90

    8 N1>20 0 -5 0

    9 (T1="Alice" OR T2="John") 0 0 0 Jack
    10 (T1="Alice" OR T2="John") 10 0 0 Alice
    11 (T1="Alice" OR T2="John") 10 0 0
    John
    12 (T1="Alice" OR T2="John") 10 0 0 Alice John
    0 (T1="Alice" AND N2=>20) 0 0 20 Alice

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Makes no sense to me but if you are happy...

    Don't delete and recreate table. Just delete records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-03-2014, 03:23 PM
  2. Replies: 7
    Last Post: 04-16-2012, 03:31 PM
  3. VBA string with OR expression
    By hmcquade in forum Forms
    Replies: 3
    Last Post: 06-24-2011, 07:59 AM
  4. Logical String Concantenation
    By Keeyter in forum Programming
    Replies: 1
    Last Post: 04-02-2010, 09:02 PM
  5. Replies: 0
    Last Post: 12-05-2005, 04:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums