Unable to create a search button and generate printable filtered report.

[Emmanuel]

I have a database which am able to filter the results to be displayed as a split form data for one record which is the date.



I used the code :

Me.Filter = "[FieldNameHere]= #" & Me.YourComboBoxNameHere & "#"
Me.FilterOn = True


I did a little research and I found another code that was able to filter the date by staff_name.

Here is the code:

Me.Filter = "[FieldNameHere]= " & Chr(34) & Me.YourComboBoxNameHere & Chr(34)
Me.FilterOn = True


I will like to create a search button that will able to filter the data by both date and staff_name.

If staff_name is left blank, search button should filter data for only date and vice versa.

I will like to be able to create a button to generate a printable report for the results from the search

Any help to be able to obtain a code to achieve this results will be greatly appreciated.

[kd2017]

you would do something like the code below to create the filter functionality. Also take a look at this example: http://allenbrowne.com/ser-62.html

    Dim criteria As String
    
    'check to see if the name combo box has a selection
    If Not IsNull(Me.YourComboBoxNameHere) Then
        criteria = "[NAME_FieldNameHere] = """ & Me.YourComboBoxNameHere & """ AND "
    End If
    
    'check to see if there is a date selected
    If Not IsNull(Me.YourDateControlHere) Then
        criteria = criteria & "[DATE_FieldNameHere] = #" & Me.YourDateControlHere & "# AND "
    End If
    
    'if the user made at least one selection then apply the filter
    If criteria <> "" Then
        'remove the trailing " AND "
        criteria = Left(criteria, Len(criteria) - 5)
        
        'apply the filter
        Me.Filter = criteria
        Me.FilterOn = True
    Else
        'remove the filter
        Me.Filter = ""
        Me.FilterOn = False
    End If

[kd2017]

Oh and as for the report you would use DoCmd.OpenReport to open your report and filter the report using the criteria string created above

DoCmd.OpenReport "YOUR REPORT NAME HERE", acViewPreview, , criteria

review this: https://docs.microsoft.com/en-us/off...cmd.openreport

[Emmanuel]

Thanks so much. I tried the codes and it worked for me.
Thanks so much for your time and assistance.

I really appreciate

[Emmanuel]

you would do something like the code below to create the filter functionality. Also take a look at this example: http://allenbrowne.com/ser-62.html

    Dim criteria As String
    
    'check to see if the name combo box has a selection
    If Not IsNull(Me.YourComboBoxNameHere) Then
        criteria = "[NAME_FieldNameHere] = """ & Me.YourComboBoxNameHere & """ AND "
    End If
    
    'check to see if there is a date selected
    If Not IsNull(Me.YourDateControlHere) Then
        criteria = criteria & "[DATE_FieldNameHere] = #" & Me.YourDateControlHere & "# AND "
    End If
    
    'if the user made at least one selection then apply the filter
    If criteria <> "" Then
        'remove the trailing " AND "
        criteria = Left(criteria, Len(criteria) - 5)
        
        'apply the filter
        Me.Filter = criteria
        Me.FilterOn = True
    Else
        'remove the filter
        Me.Filter = ""
        Me.FilterOn = False
    End If

I created another form which follows similar criteria.

In this form, we will maintain the details in the date combo but we will use a textbox instead of a combo list for the other combo.
This new textbox will pick data from a numeric field.

I tried to modify the code to meet this criteria but looks like the code for that combo doesn’t work for numeric a data.

Can you modify the code for me so it could work for a numeric data?

[Welshgasman]

Numerics do not have quotes surrounding them?

 criteria = "[NAME_FieldNameHere] = " & Me.YourNumericControl & " AND "

Strings need single quotes ' or triple double quotes " (I think)
Dates need # and be in mm/dd/yyyy or yyyy-mm-dd formats

HTH

[Emmanuel]

Numerics do not have quotes surrounding them?

 criteria = "[NAME_FieldNameHere] = " & Me.YourNumericControl & " AND "

Strings need single quotes ' or triple double quotes " (I think)
Dates need # and be in mm/dd/yyyy or yyyy-mm-dd formats

HTH

Alright. Will give it a try and see how it goes.
Thanks a lot

[Welshgasman]

Before you ever use a filter like this, put it all into a string variable., which kd2017 has done for you here.
Then you can Debug.Print it to see what you actually have, not what you think you have.

Once you get it correct, you can use the string variable. So in your case
Debug.Print criteria

You can then also copy and paste it back here, so someone can advise.

[Emmanuel]

Before you ever use a filter like this, put it all into a string variable., which kd2017 has done for you here.
Then you can Debug.Print it to see what you actually have, not what you think you have.

Once you get it correct, you can use the string variable. So in your case
Debug.Print criteria

You can then also copy and paste it back here, so someone can advise.

Thanks so much for your help.
And also the further explanations given.
I really appreciate.