• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

ADO Recordset Filter Urgent Help

Hi,
   I am using ADO recordset. I am cloning the recordset and implementing filter on it. But the filter is not working properly. Its giving an error saying
"3001- Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

My Filter is something like this

StrFilter = strFilter = strFilter & " AND " & GetUWFilterStatus(lstUWStatus, True)
(i.e. CREDITED_BRANCH_CD = 2 AND (UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED') ---- Its giving an error.

But when I am using the following one it is fetching the records

CREDITED_BRANCH_CD = 2 AND (UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED'  OR UW_STATUS_DS = 'WORKING' OR UW_STATUS_DS = 'LOST'  OR UW_STATUS_DS = 'BOUND' OR UW_STATUS_DS = 'SUSPENDED' OR UW_STATUS_DS = 'VOIDED' OR UW_STATUS_DS = 'EXTENDED'  OR UW_STATUS_DS = 'NON RENEWED' )

Please help.

Thanks in advance.

Sanjay.
0
ms_sanjay
Asked:
ms_sanjay
  • 9
  • 8
1 Solution
 
nayernaguibCommented:
Set a breakpoint at the above statement, and check for the value of GetUWFilterStatus(lstUWStatus, True). You can also use a Debug.Print statement before the above statement to have the return value of GetUWFilterStatus(lstUWStatus, True) printed in the Debug window. This will help you realize the problem.

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks for the reply. I did that and checked the value. It is returning me the follwoing string

(UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED')

But the final following StrFilter string is giving me an error.
CREDITED_BRANCH_CD = 2 AND (UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED')

Please help. Thanks in advance.

Sanjay.
0
 
weiroblpayCommented:
Your query may have the wrong syntax. Check out this article from Microsoft:

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

Basically it says this will cause the error (OR clauses within parans):

(EmployeeID = 2 OR  LastName = 'Fuller') AND FirstName = 'Andrew'

and this does not:

(EmployeeID = 2 AND FirstName = 'Andrew') OR (LastName = 'Fuller' AND FirstName = 'Andrew')


You could also use a different way of getting the data you want from a SQL query.
Example:  
strQuery = "SELECT FirstName LastName, Price FROM Invoices WHERE Price > 5000 AND LastName = 'Jones'"
   Set rsPrice = conn.Execute(strQuery)

The WHERE clause using the query is more flexible and can be dynamic if you use variables instead of actual values.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nayernaguibCommented:
Note that the author says that the code works when a similar filter value is hardcoded!

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi,
   Thanks Ron and Nayer for the reply.

As per the Microsoft knowledge base article 280811 I change the syntax but I am getting inconsistent results.

Here is my syntax
strFilter = strFilter & " OR " & GetUWFilterStatus(lstUWStatus, False)
i.e. Immediate window value CREDITED_BRANCH_CD = 2 OR (UW_STATUS_DS = 'CANCELLED' AND UW_STATUS_DS = 'QUOTED')

Its fetching the results for Branch 2 but getting results for other statuses also apart from Cancelled and Quoted. I am in a fix.

Thanks in advance.

Regards
Sanjy.
0
 
nayernaguibCommented:
Getting unwanted results in this case is normal. The logic expression:

  CREDITED_BRANCH_CD = 2 AND (UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED')

is completely different from this one:

  CREDITED_BRANCH_CD = 2 OR (UW_STATUS_DS = 'CANCELLED' AND UW_STATUS_DS = 'QUOTED')

The second expression means "get me the results where branch=2 ***or*** status is equal to ***both*** values 'cancelled' and 'quoted' at the same time"!!
Of course, status cannot take two different values in one record, so all records matching the first expression (branch=2) are returned.

You can solve this by either manually processing the string so that it looks like the one weiroblpay suggested:

  (CREDITED_BRANCH_CD = 2 AND UW_STATUS_DS = 'CANCELLED') OR (CREDITED_BRANCH_CD = 2 AND UW_STATUS_DS = 'QUOTED')

or you can simply try removing the parentheses (the above article claims that there is no precedence for AND and OR). But in this case, your filter should look like this:

  UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED' AND CREDITED_BRANCH_CD = 2

This will allow the OR clause to be evaluated first before ANDing with the last condition. Note that having the expression look like this:

  CREDITED_BRANCH_CD = 2 AND UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED'

will have a completely different meaning, and will again produce unwanted results.

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks for the reply.

I cannot manually change the string because the criteria changes depending on the user selection. Apart from credited branch the user can select other values(ex. division or status or underwriter name from drop down). so the filter string changes based on the selection.

As per the Article if I am building the string as UW_STATUS_DS = 'CANCELLED' OR UW_STATUS_DS = 'QUOTED' AND CREDITED_BRANCH_CD = 2
 then I am getting the following 3001 error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Please advise.

Thanks in advance.

Regards
Sanjay.
0
 
nayernaguibCommented:
Manually modifying the string does not mean hardcoding the string. Rather, you programmatically process the string to match the required formula:

For example, if you have the following strings at runtime:

  str1="A=5"
  str2="(B=6 OR B=7)"

Which are both unknown at design time, and you want the logic expression to become:

  str3="(A=5 AND B=6) OR (A=5 AND B=7)"

Which is equivalent to the logic expression that you want, you can do as follows:

1. Remove parentheses from str2. Now you have str2="B=6 OR B=7".
2. Break str2 into substrings tmp1, tmp2, etc... using the operator OR as a delimiter. Now you have tmp1="B=6" and tmp2="B=7".
3. Concatenate str1 and each of the substrings separately, adding an AND operator between the two strings. Now you have tmp1="A=5 AND B=6" and tmp2="A=5 AND B=7".
4. Add parentheses around each of the substrings.
5. Concatenate all substrings into one string, adding OR between each two substrings. Now you have str3="(A=5 AND B=6) OR (A=5 AND B=7)".

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
Thanks again for the reply.

I can have the following conditions
Str1 = (A=65 B='System' C='Completed')
Str2 = (D='Quoted' or D='Cancelled')

In that case how to build a consolidated string. I have 5 dropdowns and based on
the user selection I will build the Str1(Str 1 may have 1 value or 2 values or 3
values). Please find below my VB routine.

Private Sub DisplayFilterRecords()
   
Dim strFilter As String
Dim lbShowData As Boolean
Dim lbFromUWStatus As Boolean

    On Error GoTo DisplayFilterRecords_err
   
    Screen.MousePointer = vbHourglass
    strFilter = ""
    lbShowData = False
    lbFromUWStatus = False
    Set mrstRenewalDataClone = Nothing
    Set mrstRenewalDataClone = mrstRenewalData.Clone
         
    strFilter = IsFilterPresent
   
    If strFilter <> "" Then
        If cboUnderwriter <> "" Then
            If cboUnderwriter <> "All" Then
                If cboUnderwriter = "NULL" Then
                    strFilter = strFilter & " AND USER_NM = ''"
                Else
                    strFilter = strFilter & " AND USER_NM = '" & cboUnderwriter & "' "
                End If
            End If
        End If
       
        If cboPolicyDivision <> "" Then
            If cboPolicyDivision <> "All" Then
                strFilter = strFilter & " AND DIVISION_NO = " & cboPolicyDivision
            End If
        End If
       
        If cboStatus <> "" Then
            If cboStatus <> "All" Then
                strFilter = strFilter & " AND STATUS_DS = '" & cboStatus & "' "
            End If
        End If
       
        If IsSelected(lstUWStatus) Then
            If GetUWFilterStatus(lstUWStatus, False) <> "All" Then
                strFilter = strFilter & " AND " & GetUWFilterStatus(lstUWStatus, False)
            End If
        End If
       
        If IsSelected(lstCreditedRegion) Then
            If GetFilterCreditedRegion(lstCreditedRegion, False) <> "All" Then
                strFilter = strFilter & " AND " & GetFilterCreditedRegion(lstCreditedRegion, False)
            End If
        End If
       
        'If cboRegion <> "" Then
            'If cboRegion <> "All" Then
                'strFilter = strFilter & " AND CREDITED_REGION_ID = " & GetText(cboRegion)
            'End If
        'End If
       
        If cboWorkingBranch <> "" Then
            If cboWorkingBranch <> "All" Then
                strFilter = strFilter & " AND WORKING_BRANCH_CD = " & GetText(cboWorkingBranch)
            End If
        End If
       
        If cboBranch <> "" Then
            If cboBranch <> "All" Then
                strFilter = strFilter & " AND CREDITED_BRANCH_CD = " & GetText(cboBranch)
            End If
        End If
       
        If strFilter <> "" Then
            If cboPolicyDivision = "All" Then
                strFilter = strFilter & " OR (DIVISION_NO = 8 AND DIVISION_NO = 26 AND DIVISION_NO = 32 AND DIVISION_NO = 37 AND DIVISION_NO = 65 AND DIVISION_NO = 68 AND DIVISION_NO = 75 AND DIVISION_NO = 88)"
            End If
           
            If cboStatus = "All" Then
                strFilter = strFilter & " OR (STATUS_DS = 'BOUND' AND STATUS_DS = 'CANCELLED' AND STATUS_DS = 'DECLINED' AND STATUS_DS = 'BOOKED' AND STATUS_DS = 'LOST' AND STATUS_DS = 'QUOTED' AND STATUS_DS = 'RETURNED TO REGION' AND STATUS_DS = 'SUSPENDED' AND STATUS_DS = 'VOIDED' AND STATUS_DS = 'WORKING' AND STATUS_DS = 'EXTENDED' AND STATUS_DS = 'NON RENEWED')"
            End If
           
            If cboUnderwriter = "All" Then
                strFilter = strFilter & GetUWFilterName(True, False)
            End If
           
            'If cboRegion = "All" Then
                'strFilter = strFilter & " OR (CREDITED_REGION_ID = 01 AND CREDITED_REGION_ID = 02 AND CREDITED_REGION_ID = 03 AND CREDITED_REGION_ID = 04 AND CREDITED_REGION_ID = 05 AND CREDITED_REGION_ID = 07 AND CREDITED_REGION_ID = 08 AND CREDITED_REGION_ID = 09 AND CREDITED_REGION_ID = 10 AND CREDITED_REGION_ID = 11 AND CREDITED_REGION_ID = 14 AND CREDITED_REGION_ID = 15 AND CREDITED_REGION_ID = 19 AND CREDITED_REGION_ID = 59 AND CREDITED_REGION_ID = 69 AND CREDITED_REGION_ID = 89 AND CREDITED_REGION_ID = 99 )"
            'End If
           
            If cboWorkingBranch = "All" Then
                strFilter = strFilter & GetFilterWorkingBranchCode(True, False)
            End If
           
            If cboBranch = "All" Then
                strFilter = strFilter & GetFilterBranchCode(True, False)
            End If
           
            If GetUWFilterStatus(lstUWStatus, False) = "All" Then
                strFilter = strFilter & " OR (UW_STATUS_DS = 'BOUND' AND UW_STATUS_DS = 'CANCELLED' AND UW_STATUS_DS = 'DECLINED' AND UW_STATUS_DS = 'BOOKED' AND UW_STATUS_DS = 'LOST' AND UW_STATUS_DS = 'QUOTED' AND UW_STATUS_DS = 'RETURNED TO REGION' AND UW_STATUS_DS = 'SUSPENDED' AND UW_STATUS_DS = 'VOIDED' AND UW_STATUS_DS = 'WORKING' AND UW_STATUS_DS = 'EXTENDED' AND UW_STATUS_DS = 'NON RENEWED')"
            End If
           
            If GetFilterCreditedRegion(lstCreditedRegion, False) = "All" Then
                strFilter = strFilter & " OR (CREDITED_REGION_ID = 01 AND CREDITED_REGION_ID = 02 AND CREDITED_REGION_ID = 03 AND CREDITED_REGION_ID = 04 AND CREDITED_REGION_ID = 05 AND CREDITED_REGION_ID = 07 AND CREDITED_REGION_ID = 08 AND CREDITED_REGION_ID = 09 AND CREDITED_REGION_ID = 10 AND CREDITED_REGION_ID = 11 AND CREDITED_REGION_ID = 14 AND CREDITED_REGION_ID = 15 AND CREDITED_REGION_ID = 19 AND CREDITED_REGION_ID = 59 AND CREDITED_REGION_ID = 69 AND CREDITED_REGION_ID = 89 AND CREDITED_REGION_ID = 99 )"
            End If
        End If
        lbShowData = True
    Else
        If cboPolicyDivision <> "" Then
            If cboPolicyDivision <> "All" Then
                strFilter = "DIVISION_NO = " & cboPolicyDivision
            End If
        End If
       
        If cboUnderwriter <> "" Then
            If cboUnderwriter <> "All" Then
                If strFilter <> "" Then
                    If cboUnderwriter = "NULL" Then
                        strFilter = strFilter & " AND USER_NM = ''"
                    Else
                        strFilter = strFilter & " AND USER_NM = '" & cboUnderwriter & "' "
                    End If
                Else
                    If cboUnderwriter = "NULL" Then
                        strFilter = "USER_NM = ''"
                    Else
                        strFilter = "USER_NM = '" & cboUnderwriter & "' "
                    End If
                End If
            End If
        End If
       
        If cboStatus <> "" Then
            If cboStatus <> "All" Then
                If strFilter <> "" Then
                    strFilter = strFilter & " AND STATUS_DS = '" & cboStatus & "' "
                Else
                    strFilter = "STATUS_DS = '" & cboStatus & "' "
                End If
            End If
        End If
       
        'If cboRegion <> "" Then
            'If cboRegion <> "All" Then
                'If strFilter <> "" Then
                    'strFilter = strFilter & " AND CREDITED_REGION_ID = " & GetText(cboRegion)
                'Else
                    'strFilter = "CREDITED_REGION_ID = " & GetText(cboRegion)
                'End If
            'End If
        'End If
       
        If cboWorkingBranch <> "" Then
            If cboWorkingBranch <> "All" Then
                If strFilter <> "" Then
                    strFilter = strFilter & " AND WORKING_BRANCH_CD = " & GetText(cboWorkingBranch)
                Else
                    strFilter = "WORKING_BRANCH_CD = " & GetText(cboWorkingBranch)
                End If
            End If
        End If
       
        If cboBranch <> "" Then
            If cboBranch <> "All" Then
                If strFilter <> "" Then
                    strFilter = strFilter & " AND CREDITED_BRANCH_CD = " & GetText(cboBranch)
                Else
                    strFilter = "CREDITED_BRANCH_CD = " & GetText(cboBranch)
                End If
            End If
        End If
       
        If IsSelected(lstUWStatus) Then
            If GetUWFilterStatus(lstUWStatus, False) <> "All" Then
                If strFilter <> "" Then
                    If gbMultipleSelected Then
                        strFilter = strFilter & " OR " & GetUWFilterStatus(lstUWStatus, False)
                        'strFilter = "(" & strFilter & ")" & " OR " & GetUWFilterStatus(lstUWStatus, False)
                        'strFilter = GetUWFilterStatus(lstUWStatus, True) & " AND " & strFilter
                        'strFilter = "(CREDITED_BRANCH_CD = 2 AND UW_STATUS_DS = 'CANCELLED') OR (CREDITED_BRANCH_CD = 2 AND UW_STATUS_DS = 'QUOTED')"
                        gbMultipleSelected = False
                    Else
                        strFilter = strFilter & " AND " & GetUWFilterStatus(lstUWStatus, True)
                    End If
                Else
                    strFilter = GetUWFilterStatus(lstUWStatus, True)
                End If
            End If
        End If
       
        If IsSelected(lstCreditedRegion) Then
            If GetFilterCreditedRegion(lstCreditedRegion, False) <> "All" Then
                If strFilter <> "" Then
                    If gbMultipleSelected Then
                        strFilter = strFilter & " AND " & GetFilterCreditedRegion(lstCreditedRegion, True)
                        gbMultipleSelected = False
                    Else
                        strFilter = strFilter & " AND " & GetFilterCreditedRegion(lstCreditedRegion, True)
                    End If
                Else
                    strFilter = GetFilterCreditedRegion(lstCreditedRegion, True)
                End If
            End If
        End If
       
        If strFilter <> "" Then
            If cboPolicyDivision = "All" Then
                strFilter = strFilter & " OR (DIVISION_NO = 8 AND DIVISION_NO = 26 AND DIVISION_NO = 32 AND DIVISION_NO = 37 AND DIVISION_NO = 65 AND DIVISION_NO = 68 AND DIVISION_NO = 75 AND DIVISION_NO = 88)"
            End If
           
            If cboStatus = "All" Then
                strFilter = strFilter & " OR (STATUS_DS = 'BOUND' AND STATUS_DS = 'CANCELLED' AND STATUS_DS = 'DECLINED' AND STATUS_DS = 'BOOKED' AND STATUS_DS = 'LOST' AND STATUS_DS = 'QUOTED' AND STATUS_DS = 'RETURNED TO REGION' AND STATUS_DS = 'SUSPENDED' AND STATUS_DS = 'VOIDED' AND STATUS_DS = 'WORKING' AND STATUS_DS = 'EXTENDED' AND STATUS_DS = 'NON RENEWED')"
            End If
           
            If cboUnderwriter = "All" Then
                strFilter = strFilter & GetUWFilterName(True, False)
            End If
           
            'If cboRegion = "All" Then
                'strFilter = strFilter & " OR (CREDITED_REGION_ID = 01 AND CREDITED_REGION_ID = 02 AND CREDITED_REGION_ID = 03 AND CREDITED_REGION_ID = 04 AND CREDITED_REGION_ID = 05 AND CREDITED_REGION_ID = 07 AND CREDITED_REGION_ID = 08 AND CREDITED_REGION_ID = 09 AND CREDITED_REGION_ID = 10 AND CREDITED_REGION_ID = 11 AND CREDITED_REGION_ID = 14 AND CREDITED_REGION_ID = 15 AND CREDITED_REGION_ID = 19 AND CREDITED_REGION_ID = 59 AND CREDITED_REGION_ID = 69 AND CREDITED_REGION_ID = 89 AND CREDITED_REGION_ID = 99 )"
            'End If
           
            If cboWorkingBranch = "All" Then
                strFilter = strFilter & GetFilterWorkingBranchCode(True, False)
            End If
           
            If cboBranch = "All" Then
                strFilter = strFilter & GetFilterBranchCode(True, False)
            End If
           
            If GetUWFilterStatus(lstUWStatus) = "All" Then
                strFilter = strFilter & " OR (UW_STATUS_DS = 'BOUND' AND UW_STATUS_DS = 'CANCELLED' AND UW_STATUS_DS = 'DECLINED' AND UW_STATUS_DS = 'BOOKED' AND UW_STATUS_DS = 'LOST' AND UW_STATUS_DS = 'QUOTED' AND UW_STATUS_DS = 'RETURNED TO REGION' AND UW_STATUS_DS = 'SUSPENDED' AND UW_STATUS_DS = 'VOIDED' AND UW_STATUS_DS = 'WORKING' AND UW_STATUS_DS = 'EXTENDED' AND UW_STATUS_DS = 'NON RENEWED')"
            End If
           
            If GetFilterCreditedRegion(lstCreditedRegion) = "All" Then
                strFilter = strFilter & " OR (CREDITED_REGION_ID = 01 AND CREDITED_REGION_ID = 02 AND CREDITED_REGION_ID = 03 AND CREDITED_REGION_ID = 04 AND CREDITED_REGION_ID = 05 AND CREDITED_REGION_ID = 07 AND CREDITED_REGION_ID = 08 AND CREDITED_REGION_ID = 09 AND CREDITED_REGION_ID = 10 AND CREDITED_REGION_ID = 11 AND CREDITED_REGION_ID = 14 AND CREDITED_REGION_ID = 15 AND CREDITED_REGION_ID = 19 AND CREDITED_REGION_ID = 59 AND CREDITED_REGION_ID = 69 AND CREDITED_REGION_ID = 89 AND CREDITED_REGION_ID = 99 )"
            End If
           
        End If
        lbShowData = True
    End If
   
    If lbShowData Then
        mrstRenewalDataClone.Filter = strFilter
        Load_Filter_Data mrstRenewalDataClone
        DBSearchResult.Refresh
        Get_Policy_Prem_Val mrstRenewalDataClone
        txtOutOf = gvRecordCount
        txtRecordCount = mvTotalGridRows + 1
        lbShowData = False
    End If
       
    Screen.MousePointer = vbDefault
   
DisplayFilterRecords_Exit:
    Screen.MousePointer = vbDefault
    Exit Sub
DisplayFilterRecords_err:
    If Err.Number = 3001 Then
        MsgBox "No Records found for the selected criteria.", vbOKOnly + vbInformation, MSGBOX_TITLE
        Screen.MousePointer = vbDefault
        Resume DisplayFilterRecords_Exit
    Else
        MsgBox Err.Description, vbOKOnly + vbInformation, MSGBOX_TITLE
        Screen.MousePointer = vbDefault
        Resume DisplayFilterRecords_Exit
    End If
End Sub

Hope you got it.

Regards
Sanjay.



0
 
nayernaguibCommented:
I can see that you have tried the following:

  strFilter = GetUWFilterStatus(lstUWStatus, True) & " AND " & strFilter

But without removing the parentheses from the string returned by GetUWFilterStatus().

As I said before, the MS article says that AND and OR have no precedence over each other. This means that an expression containing multiple AND/OR operators is evaluated from left to right. The article also claims that you get an error *only* when you use an expression in the form:

  (A OR B) AND C

Try removing the parentheses from the first expression, and if this does not work, I can explain how you can apply the generic string processing that I have mentioned in my previous post.

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks for the reply. I tried removing the parenthesis but still its not working. Any help would be greatly appreciated.

Thanks in advance.

Regards
Sanjay.
0
 
nayernaguibCommented:
OK! Now back to the first method. If the user makes multiple selections, and you use the AND operator to join these conditions, then the algorithm described above still holds true. In other words, you treat the ANDed expression as *one* string.

For example, if we have:

  str1="A=w AND B<x AND C>y and D=z"
  str2="E=k or E=l or E=m"

Then we can treat str1 exactly as in the previous example ("A=5"). Now follow these steps:

* You break down str2 into tmp(1)="E=k", tmp(2)="E=l", and tmp(3)="E=m". This can be done by declaring an array of strings whose size is equal to the number of OR occurences in str2 plus one. Next, you use string manipulation fuctions available in VB (Left(), InStr(), Mid(), etc...) to extract portions of str2.
* You set tmp(1)="(" str1 & " AND " & tmp(1) & ")", tmp(2)="(" str1 & " AND " & tmp(2) & ")", and tmp(3)="(" str1 & " AND " & tmp(3) & ")".
* You set str3=tmp(1) & " OR " & tmp(2) & " OR " & tmp(3).

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks for the reply and suggestion. I will try doing this and will keep you posted.

Thanks again.

Regards
Sanjay Samuel.
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks for the reply. It must annoying as I pasted all the code. Sorry about that. The suggestion seems to be good but the problem is I have 12 filters on the screen and building a string with so many filters seems to be little....... you know what I am saying. What do you say about this. Let me know.

Thanks again for your help.

Regards
Sanjay.
0
 
nayernaguibCommented:
Try this code:

_________________________________________________

Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim count As Integer
Dim pos As Integer

str1 = "A=5 AND B=5 AND C<7" 'combination of conditions specified by user
str2 = "(D=1 OR D=2 OR D=3 OR D=4)" 'the second string
str3 = ""

pos = 0
count = 0 'this will hold the number of OR's contained in str2
str2 = Mid(str2, 2, Len(str2) - 2) 'to remove parentheses
Do
  pos = InStr(pos + 1, str2, " OR")
  If pos <> 0 Then count = count + 1
Loop Until pos = 0

'now count holds the number of OR occurences
ReDim temp(count + 1) As String
'temp will hold the count+1 substrings
For i = 1 To count + 1
  If InStr(1, str2, " OR") <> 0 Then
    temp(i) = Left(str2, InStr(1, str2, " OR") - 1)
  Else
    temp(i) = str2
  End If
  str2 = Mid(str2, InStr(1, str2, " OR") + 4)
Next

For i = 1 To count + 1
  temp(i) = "(" & str1 & " AND " & temp(i) & ")"
  If i = 1 Then
    str3 = temp(i)
  Else
    str3 = str3 & " OR " & temp(i)
  End If
Next
'and here you are! :-)
'str3 now holds the required value
'"(A=5 AND B=5 AND C<7 AND D=1) OR (A=5 AND B=5 AND C<7 AND D=2) OR (A=5 AND B=5 AND C<7 AND D=3) OR (A=5 AND B=5 AND C<7 AND D=4)"

_________________________________________________

_______________

  Nayer Naguib
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks a bunch. Let me try this code and will let you know how it goes.

Thanks again.

Regards
Sanjay.
0
 
ms_sanjayAuthor Commented:
Hi Nayer,
   Thanks. Can you give me your email id.

Regards
Sanjay.
0
 
nayernaguibCommented:
I'm afraid posting e-mail addresses is prohibited on Experts Exchange.
Maybe you would like to take a look at this freeware utility:

  http://www.simtel.net/product.php%5Bid%5D54399%5Bcid%5D301%5BSiteID%5Dsimtel.net

_______________

  Nayer Naguib
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now