issues with filtering same field twice with 2 combo box's (1 Viewer)

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
hi,
all

I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient

works great

problem is I have now added another cboclient2 to filter client name twice

so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report
but doesn't apply the date range to cboclient2 it just show's all records with that client name

here's the code
Code:
 Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it 
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
        'Put your report name in these quotes.
        strReport = "Input Report"
  If Me.Check10 = True Then
   strDateField = "[Date raised]" 'Put your field name in the square brackets in these quotes
    ElseIf Me.Check10 = False Then
    strDateField = "[Date Work Completed]"
    End If
     lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        
        
    End If
    
    If Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "')"
End If
[COLOR=red] If Len(Trim(Me.cboclient2)) > 0 Then
    If strWhere <> vbNullString Then
        strWhere = strWhere & [COLOR=blue]" OR "
[/COLOR]    End If
    strWhere = strWhere & "(Client = '" & Me.cboclient2 & "')"[/COLOR]
End If
     'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
 Exit_Handler:
    Exit Sub
 Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

I suspect the problem is to do with "or" in my statement for cboclient2
I have try "AND" get a syntax error

im struck on what to try


any help much appreciated

cheers

shane
 

spikepl

Eledittingent Beliped
Local time
Today, 11:39
Joined
Nov 3, 2010
Messages
6,144
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
Do remove the single quote and inspect the result. Paste it into the SQL view of the query designer at the end of the reports queryn and change it until happy. Then you know what your code must produce.
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
hi

I tried debug print this is result looks right to me

Code:
 ([Date Raised] >= #10/03/2013#) AND ([Date Raised] < #10/24/2013#) AND (Client = 'S J Turner') OR (Client = 'Cavendish')
can't understand why the value enter in cboclient2 does not apply to date range enter it show's all records with that client name but cboclient shows all records with client name enter in it but with in the date range
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:39
Joined
Aug 30, 2003
Messages
36,118
You want the 2 client tests enclosed in parentheses to clarify the logic. Right now you basically have:

1 And 2 And 3 Or 4

I think you want:

1 And 2 And (3 Or 4)

The parentheses you have are all meaningless.
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
You want the 2 client tests enclosed in parentheses to clarify the logic. Right now you basically have:

1 And 2 And 3 Or 4

I think you want:

1 And 2 And (3 Or 4)

The parentheses you have are all meaningless.

Paul

i'm guessing your saying put 3 and 4 together in one statement with a or to be honest Paul I m way over my head with this one would not no where to start

thanks again

shane
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
right I have try this
Code:
     If Len(Trim(Me.cboclient)) & (Trim(Me.cboclient2)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "OR" & Me.cboclient2 & "')"
End If

This doesn't work can someone point me in the right direction please!!!!!!!!!!!!!!!

thanks in advance

shane
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
hi have tried

Code:
     If Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & "and"
End If
 If Len(Trim(Me.cboclient2)) > 0 Then
 End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "or" & Me.cboclient2 & "')"
End If
still no good I'm running out of try's lol
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:39
Joined
Aug 30, 2003
Messages
36,118
Using Debug.Print on that should lead you to the problem. Note that other than parentheses the syntax was correct before.
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
Paul can you point me in direction of which parentheses I need to change or add

pretty please with a cherry on top

cheers
shane
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:39
Joined
Aug 30, 2003
Messages
36,118
It's not a matter of parentheses now. You didn't repeat the field name.
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
ok I have now try this still can't bloody get it to work

Code:
      If Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & "and"
End If
 If Len(Trim(Me.cboclient2)) > 0 Then
 End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "')" & "or" & (Client = '" Me.cboclient2 "')"
End If
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
im getting this when I debug print

Code:
([Date raised] >= #12/01/2012#) AND ([Date raised] < #10/02/2013#)and(Client = 'Luton Council')orFalse
 

pr2-eugin

Super Moderator
Local time
Today, 10:39
Joined
Nov 30, 2011
Messages
8,494
There is NO space between criteria.. Also you are missing a field name.

([Date raised] >= #12/01/2012#) AND ([Date raised] < #10/02/2013#)<insertSpace>and<insertSpace>(Client = 'Luton Council')<insertSpace>or<insertSpace><whatField>= False
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:39
Joined
Aug 30, 2003
Messages
36,118
Why are you including parentheses in the middle again? I said the parentheses were fine on that effort, just the field name problem.
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
hi
right I try adding field name and remove parentheses in the middle
Code:
If Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & "and"
End If
If Len(Trim(Me.cboclient2)) > 0 Then
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "or" & Client = Me.cboclient2 & "')" 
                                                           '  ^ added field name
End If
debug print shows all false still not working GGGGGGGGRRRRRR

thanks for you time lads sorry Im bloody useless
 
Last edited:

pr2-eugin

Super Moderator
Local time
Today, 10:39
Joined
Nov 30, 2011
Messages
8,494
Show the complete code you have so far ! Please Indent the CODE and also make sure you add code tags.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:39
Joined
Aug 30, 2003
Messages
36,118
I guess Paul is taking over, so I'll get out of the way.
 

pr2-eugin

Super Moderator
Local time
Today, 10:39
Joined
Nov 30, 2011
Messages
8,494
I guess Paul is taking over, so I'll get out of the way.
No Paul, I am not taking over, we are talking the same thing, I am just asking a different question to see if there is more to the issue ! :eek:
 

sspreyer

Registered User.
Local time
Today, 03:39
Joined
Nov 18, 2013
Messages
251
Right Paul and Paul

here's the hole the code

Code:
  Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
        'Put your report name in these quotes.
        strReport = "Input Report"
  If Me.Check10 = True Then
   strDateField = "[Date Raised]" 'Put your field name in the square brackets in these quotes
    ElseIf Me.Check10 = False Then
    strDateField = "[Date Work Completed]"
    End If
     lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        
[COLOR=red]   If Len(Trim(Me.cboclient)) & (Trim(Me.cboclient2)) > 0 Then
      End If
If strWhere <> vbNullString Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "OR" & [Client] = Me.cboclient2 & "')"[/COLOR]
 [COLOR=red]End If[/COLOR]
 Debug.Print strWhere
      'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
right the code in red my issue
the code originates from Allen Brown "where dates"
I have just modify some of it

p.s paul and paul any help much appreciated the way I see it 2 heads are better then one specially when your trying to help me!!!!!! sorry boyz if I'm take to long to get this right

cheer

shane
 

pr2-eugin

Super Moderator
Local time
Today, 10:39
Joined
Nov 30, 2011
Messages
8,494
When you properly indent your code, you might be able to identify your problem straight away. This is your properly indented code !
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler    
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"      'Do NOT change it to match your local settings.
    
    [COLOR=Green]'DO set the values in the next 3 lines.
    'Put your report name in these quotes.[/COLOR]
        
    strReport = "Input Report"
    
    If Me.Check10 = True Then
        strDateField = "[Date raised]"            [COLOR=Green] 'Put your field name in the square [/COLOR]brackets in these quotes
    Else
        strDateField = "[Date Work Completed]"
    End If
    
    lngView = acViewReport                        [COLOR=Green] 'Use acViewNormal to print instead of preview.[/COLOR]
    
    [COLOR=Green]'Build the filter string.[/COLOR]
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    
    [COLOR=Blue]If IsDate(Me.txtEndDate) Then[/COLOR]
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
        
[COLOR=Red]        If Len(Trim(Me.cboclient)) & (Trim(Me.cboclient2)) > 0 Then
      
        End If[/COLOR]
    
        If strWhere <> vbNullString Then
            strWhere = strWhere & " and "
        End If
        strWhere = strWhere & "(Client = '" & Me.cboclient & "OR" & [Client] = Me.cboclient2 & "')"
   [COLOR=Blue] End If[/COLOR]
    
    [COLOR=Green] 'Close the report if already open: otherwise it won't filter properly.[/COLOR]
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    [COLOR=Green]'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.[/COLOR]
    DoCmd.OpenReport strReport, lngView, , strWhere
 Exit_Handler:
    Exit Sub
 Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
Why do you have an If dangling?
 

Users who are viewing this thread

Top Bottom