Incorrect SQL Statement while using more than one criteria

V

VSolano

Registered User.
Local time
Yesterday, 22:35
Joined
Feb 21, 2017
Messages
97
I am looking for some help from this community.

I copy the following code to filter a list box and various combo boxes. I am having a hart time adding more code to the STRWhere statement. When I select items from the list box and also select an item from the combo box, the information is working fine. the problem is when nothing is selected on the combo box the report bring a mismatch. I tried the null value and the zero and nothing is working.

I just need help on adding more filtering data to the STRWhere statement.
It will be like five options to select from

Code: 
Private Sub brnFilter_Click()
On Error GoTo ProblemHandle

Dim DB As Database
Dim RS As Recordset
Dim I As Integer
Dim StrSQL As String
Dim VarianteValue As Variant
Dim StrWhere As String
Dim StrIn As String
Dim FlagSelectAll As Boolean
Dim BEntity As Long
Dim BCoCode As Long
Dim STREntity As String
BEntity = Nz(Me.cboentity)





Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT * FROM tbbilling")


   

    For I = 0 To Me.ListBox.ListCount - 1
        If Me.ListBox.Selected(I) Then
            If Me.ListBox.Column(0, I) = "ALL" Then
                FlagSelectAll = True
            End If
            StrIn = StrIn & "" & Me.ListBox.Column(0, I) & ","
            
        End If
        
'  
        
        
    Next I
    

    
            

    
    
    
     StrWhere = " WHERE [tbbilling.benefitsID] in " & _
               "(" & Left(StrIn, Len(StrIn) - 1) & ")" _
               & " AND [tbbilling.entityid] = " & BEntity & ""
               

    
   
    
   StrSQL = "INSERT INTO tbbillingReport SELECT tbbilling.* FROM tbbilling "
    
    
  
    
        StrSQL = StrSQL & StrWhere
    Debug.Print StrSQL
    
    End If
    

    DoCmd.RunSQL StrSQL

    
    
    

  
    For Each VarianteValue In Me.ListBox.ItemsSelected
    
        Me.ListBox.Selected(VarianteValue) = False
    Next VarianteValue






Exit Sub


        
        
   
    


DB.Close
Set DB = Nothing
RS.Close

End Sub
 
Last edited:
Please use the code tags - your code is otherwise very difficult to read and will put off people responding.
 
when you paste your code into your post, highlight it and click the code button (the # button)
 
How do I add another where criteria to the code you send on the link.like aso pulling data from a combo box
 
you have to put some effort in.
Code: 
Private Sub brnFilter_Click()
On Error GoTo ProblemHandle

   Dim DB As Database 
   Dim RS As Recordset
   Dim I As Integer
   Dim StrSQL As String
   Dim VarianteValue As Variant
   Dim StrWhere As String
   Dim StrIn As String
   Dim FlagSelectAll As Boolean
   Dim BEntity As Long
   Dim BCoCode As Long
   Dim STREntity As String
   
   BEntity = Nz(Me.cboentity)
   Set DB = CurrentDb
   Set RS = DB.OpenRecordset("SELECT * FROM tbbilling")
   'Build the IN string by looping through the listbox
   For I = 0 To Me.ListBox.ListCount - 1
     If Me.ListBox.Selected(I) Then
       If Me.ListBox.Column(0, I) = "ALL" Then
         FlagSelectAll = True
       End If
       StrIn = StrIn & "" & Me.ListBox.Column(0, I) & ","
    End If
' Debug.Print StrIn
   Next I
' If (BEntity = " ") Then
'    STREntity = "LIKE '*'"
' Else
'    STREntity = "='" & BEntity & ""
' End If
  'Create the WHERE string, and strip off the last comma of the IN string
   StrWhere = " WHERE [tbbilling.benefitsID] in " & _
   "(" & Left(StrIn, Len(StrIn) - 1) & ")" _
   & " AND [tbbilling.entityid] = " & BEntity & ""

   Debug.Print StrWhere
  'Empty table content
  Call EmptyTbl
  'Append filted data
   StrSQL = "INSERT INTO tbbillingReport SELECT tbbilling.* FROM tbbilling "
  'If "All" was selected in the listbox, don't add the WHERE condition
   If Not flgSelectAll Then
     StrSQL = StrSQL & StrWhere
     Debug.Print StrSQL
   End If
  ' DoCmd.SetWarnings False
    DoCmd.RunSQL StrSQL
  ' DoCmd.SetWarnings True
  ' Debug.Print StrSQL
  'Clear listbox selection after running query
   For Each VarianteValue In Me.ListBox.ItemsSelected
      me.ListBox.Selected(VarianteValue) = False
   Next VarianteValue
 Exit Sub
ProblemHandle:
  Select Case Err.Number
    Case Is = 5
      MsgBox "You must make a selection(s) from the list" , , "Selection Required !"
    Case Else
      MsgBox ("Error " & Err.Number & " " & Err.Description & "!")
  End Select
Resume Next
   DB.Close
  Set DB = Nothing
  RS.Close
End Sub
 
I am not an expert.

I just try to get some guidance.
 
I am just trying to add another criteria here.

Code: 
 [CODE]StrWhere = " WHERE [tbbilling.benefitsID] in " & _
               "(" & Left(StrIn, Len(StrIn) - 1) & ")"
 
If you want to add one WHERE criteria based on a combo, then you have to test for it having no selection. If not, don't concatenate. If yes, then do concatenate. However, I think there are issues that should be taken care of first:

BEntity = Nz(Me.cboentity) - no point in Nz function if you're not going to provide a value for when Null
You're creating a recordset but don't use it as far as I can see. Might as well remove any rs code
Me.ListBox - you really have a listbox named "listbox"?
Personally I don't see the sense in a listbox where a list item can be "ALL". I would provide some other means; perhaps option group with All and some other option. Disable listbox if All is chosen.
 
Last edited by a moderator:
I did not copy the full sub.

the recordset is just pulling the information the corresponding table to create a new one as filtered.

The information for BEntity this the additional on the combo box that I want to add it to the criteria
 
VSolano said:
I am looking for some help from this community.

I copy the following code to filter a list box and various combo boxes. I am having a hart time adding more code to the STRWhere statement. When I select items from the list box and also select an item from the combo box, the information is working fine. the problem is when nothing is selected on the combo box the report bring a mismatch. I tried the null value and the zero and nothing is working.

I just need help on adding more filtering data to the STRWhere statement.
It will be like five options to select from

Code: 
Private Sub brnFilter_Click()
On Error GoTo ProblemHandle

Dim DB As Database
Dim RS As Recordset
Dim I As Integer
Dim StrSQL As String
Dim VarianteValue As Variant
Dim StrWhere As String
Dim StrIn As String
Dim FlagSelectAll As Boolean
Dim BEntity As Long
Dim BCoCode As Long
Dim STREntity As String
BEntity = Nz(Me.cboentity)





Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT * FROM tbbilling")


   

    For I = 0 To Me.ListBox.ListCount - 1
        If Me.ListBox.Selected(I) Then
            If Me.ListBox.Column(0, I) = "ALL" Then
                FlagSelectAll = True
            End If
            StrIn = StrIn & "" & Me.ListBox.Column(0, I) & ","
            
        End If
        
'  
        
        
    Next I
    

    
            

    
    
    
     StrWhere = " WHERE [tbbilling.benefitsID] in " & _
               "(" & Left(StrIn, Len(StrIn) - 1) & ")" _
               & " AND [tbbilling.entityid] = " & BEntity & ""
               

    
   
    
   StrSQL = "INSERT INTO tbbillingReport SELECT tbbilling.* FROM tbbilling "
    
    
  
    
        StrSQL = StrSQL & StrWhere
    Debug.Print StrSQL
    
    End If
    

    DoCmd.RunSQL StrSQL

    
    
    

  
    For Each VarianteValue In Me.ListBox.ItemsSelected
    
        Me.ListBox.Selected(VarianteValue) = False
    Next VarianteValue






Exit Sub


        
        
   
    


DB.Close
Set DB = Nothing
RS.Close

End Sub
Click to expand...
I am not an expert in this because I am working for a shopfront company but I do get great knowledge from your post.
 
You must log in or register to reply here.

Similar threads

G
Setting up Sql Search to Sql Server backend using Connections
Replies
4
Views
134
Geirr
G
S
Getting total in one line of the query
Replies
13
Views
830
silentwolf
S
J
Solved Single Quotes in Criteria
Replies
4
Views
646
plog
P
A
Access Audittrail
Replies
5
Views
885
CJ_London
C
T
Solved It takes too long to download data from server
Replies
8
Views
838
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom