MS Access VBA Converting a variable length text string(from a memo table field) to a boolean rule RRS feed

  • Question

  • Hi,

       Hope you are well. I have been racking my brains on how to do the following but have come up with nothing so far. I am trying to convert a text string into a Boolean validation rule to validate a second comma separated string. I will explain further:

    My first string would be variable length and comma separated = string1. This would be read in from a table which we would call Table1

    e.g.*** string1 read in from 1st record of Table1. Codes are 5 or 3 characters long.

    string1 = ",ABD86,SGS54,FGD45,DHE78,DHF46,KLA23,PAR"

    A 2nd variable length string would read in from Table2. This will be used to evaluate string 1 and return true or false.

    e.g.*** string2 read in from table 2 to validate string1.

    string2 = "(SGS54 | ZFD12) & (PAR | BGD)"

    With Boolean syntax Or = '|' / And = '&' / Not = '!'

    How can I convert string 2 from a text string to an actual Boolean rule? with equivalent

    Bool result = string1 contains (SGS54 Or ZFD12) And contains (PAR Or BGD)

    *** Which would convert to

    True = string1 contains (True Or False) And (True Or False) 

    I would like to do this with multiple strings in both Table1 and Table2, that have varying number of terms using Or = '|' / And = '&' / Not = '!'

    Thanks in advance

    Monday, February 6, 2017 9:14 PM

All replies

  • Hi Stephen,

    Thanks for visiting our forum.

    Then this forum mainly focus on general questions and feedback about Office client. Since your query is more related to developing issues involving Access, I'll move your thread to the following dedicated MSDN forum for Access:


    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.

    Best regards,
    Yuki Sun

    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, February 7, 2017 2:21 AM
  • Hi,

    We could use RegExp "\w+" to match the any word characters and then check if string one contains the several strings and returns 1/0.


    Dim a, b As String
    a = ",ABD86,SGS54,FGD45,DHE78,DHF46,KLA23,PAR"
    b = "(SGS54 | ZFD12) & (PAR | BGD)"
    Set Reg = CreateObject("VBScript.RegExp")
    Reg.Pattern = "\w+"
    Reg.Global = True
    Set myMatches = Reg.Execute(b)
    For Each match In myMatches
    If InStr(1, a, match.Value) > 0 Then
    b = Replace(b, match.Value, 1)
    b = Replace(b, match.Value, 0)
    End If
    Debug.Print b

    The result is (1 | 0) & (1 | 0).

    Please check if it works for other strings in your database.

    If the reg works, for the second issue about how to calculate the Boolean expression, I would suggest you post a new thread.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen Li Friday, February 10, 2017 9:45 AM
    Thursday, February 9, 2017 2:06 AM