Report from filter form (1 Viewer)

[tihmir]

Hi all, I need some help, please
I have filter form with 2 text boxes – Date from (txt.Datefrom) and Date to txt.Dateto)…, and cbo_inspector.
My VBA code is working for Date but I wanna to set criteria and for cbo_inspector.
The code is:

  Private Sub cmd_Filter_Click()
  Dim strReport As String
  Dim strDateField As String
              Dim strWhere As String
              Dim lngView As Long
              Const strcJetDate = "\#mm\/dd\/yyyy\#"
   
  strReport = "rpt_ reference"
  strDateField = "[Date]"
  lngView = acViewPreview
   
  If IsDate(Me.txt.Datefrom) Then
          strWhere = "(" & strDateField & " >= " & Format(Me.txt.Datefrom, strcJetDate) & ")"
  End If
      If IsDate(Me.Dateto) Then
          If strWhere <> vbNullString Then
              strWhere = strWhere & " AND "
          End If
          strWhere = strWhere & "(" & strDateField & " < " & Format(Me.Dateto + 1, strcJetDate) & ")"
      End If
  If CurrentProject.AllReports(strReport).IsLoaded Then
          DoCmd.Close acReport, strReport
  End If
   
  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

Thise code is working perfect for the date from – to, but I wanna to set criteria for cbo_Inspektor, where there is field “Inspektor” in my query.
What it should be the code??? I want also to have message if the cbo_...... is empty…..” please enter a value”

 

[pbaldy]

Presuming the field is numeric it would look like:

strWhere = strWhere & " AND Inspektor = " & Me.cbo_Inspektor

 

[tihmir]

Presuming the field is numeric it would look like:

strWhere = strWhere & " AND Inspektor = " & Me.cbo_Inspektor

Where to insert strWhere = strWhere & " AND Inspektor = " & Me.cbo_Inspektor

and do I need to put "If isnoll" ?

 

[pbaldy]

Logically, after the date portion is built but before opening the report. You can certainly test for a selection being made:

If Len(Me.cbo_Inspektor & vbNullString) > 0 Then

 

[tihmir]

Logically, after the date portion is built but before opening the report. You can certainly test for a selection being made:

If Len(Me.cbo_Inspektor & vbNullString) > 0 Then

When I set:


If Len(Me.cbo_Inspektor & vbNullString) > 0 Then
strWhere = strWhere & " AND Inspektor= " & Me.cbo_Inspektor
End If
It show me error: 3075

 

[pbaldy]

You didn't address this:

Presuming the field is numeric

but if it's text it would be:

strWhere = strWhere & " AND Inspektor= '" & Me.cbo_Inspektor & "'"

 

[tihmir]

You didn't address this:



but if it's text it would be:

strWhere = strWhere & " AND Inspektor= '" & Me.cbo_Inspektor & "'"

I tried many times but it does not work! Here is my database.
Help me, please

 

[arnelgp]

Private Sub cmd_Report_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    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.
    strReport = "rpt_Report"      'Put your report name in these quotes.
    strDateField = "[Data]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtFrom, strcJetDate) & ")"
    End If
    If IsDate(Me.txtTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtTo + 1, strcJetDate) & ")"
    End If
    
    If Trim(Me.cbo_Inspector & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Inspector] = '" & Me.cbo_Inspector & "'"
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    ' check if there is atleast a record to print
    If DCount("*", "qry_Report", strWhere) = 0 Then
        MsgBox "Nothing to Print"
        Exit Sub
    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

 

[jdraw]

Cross posted at http://www.accessforums.net/showthread.php?t=74885

tihmir,
I realize you are new here, welcome.

However, there are some general points of netiquette on all forums. And one of them is cross posting-- If and when you cross post, always identify the related posts so readers don't waste time answering a thread that has been solved elsewhere. If you get a solution, have the courtesy to inform others and show a link to the accepted solution.

Readers are unpaid volunteers offering their experience and advice to assist others.

Details of how and why to cross post (from a responder) are laid out here.

 

[tihmir]

Huge thanks to arnelgp
Thank you very, very much! Your code add-on really works! That's what I was trying to do! If I could ask you how to do the cbo_Inspector must be filled in of is not = Null, then message "Please, fill cbo_Inspector"

In addition, I want to apologize for my mistake jdraw. I am so sorry

 

[arnelgp]

change this part to:

   If Trim(Me.cbo_Inspector & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Inspector] = '" & Me.cbo_Inspector & "'"
   Else
        msgbox "Please fill the Inspector combobox"
        exit sub
    End If

 

[jdraw]

tihmir,
No problem. We are all learning.
Good luck with your project.

Did the answers on the cross posted site not provide a solution?

 

[tihmir]

arnelgp, Again, thank you sincerely!You give the exact and concrete solutions to the problem!
jdraw, I'm grateful for all the advice I get here andhttps://www.access-programmers.co.uk/forums/member.php?u=31021 in cross posted sites.
Once again, I want to point out that arnelgp's help was clear, precise and concrete!

 

[jdraw]

I am glad you are grateful and have a solution.
I am also orange on the access net site.

Good luck with your project.