Search by Date and Some Text

[caykamanj1966]

All, I have a report (rpt_Expenses) with these 2 fields: [date] and [purpose]

I want to create a button on a form to bring up the report, but before it opens I want to search for a date range, for the [date] field, and I want to search a value from the [purpose] field.

I found code from dhookom, which is below:

Dim strWhere As String
strWhere = "[purpose] = """ & Me.purpose & """"
DoCmd.OpenReport "rpt_ExpRpt", acViewPreview, , strWhere

I just need to know how to incorporate searching for a date range, to include in the code.

Also, is the above correct for the [purpose] field?

Your help will be greatly appreciated.

 

[dhookom]

The WHERE CONDITION of the DoCm.OpenReport is like the criteria of a query. The form containing this code should have text boxes for the min and max date values [txtMinDate] and [txtMaxDate]. Dates are demlimited with #. You should build this to accommodate 0, 1, or 2 dates entered by the user.

Also, Date is a reserved word and should never be used as a field name. You should implement a naming convention for all your objects that isn't ambiguous. I would probably use [expDate].

Dim strWhere As String
strWhere = "[purpose] = """ & Me.purpose & """"
If Not IsNull(Me.txtMinDate) Then
    strWhere = strWhere & " AND [Date] >=#" & Me.txtMinDate & "# "
End If 
If Not IsNull(Me.txtMaxDate) Then
    strWhere = strWhere & " AND [Date] <=#" & Me.txtMaxDate& "# "
End If 
Debug.Print strWhere   [COLOR=#4E9A06]'display value in the immediate window for debugging[/color]
DoCmd.OpenReport "rpt_ExpRpt", acViewPreview, , strWhere

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

I put the code on the button to open the report and I get the following error:

If Not IsNull (Me.txtMinDate)...

The Me.txtMinDate is highlighted in yellow, and then I get the following pop dialogue box:

Compile error:

Method or data member not found

I actually put the txtMinDate and txtMaxDate field on the report.

Does those fields need to go on the form where the button to click the report is?

 

[dhookom]

As I stated “ The form containing this code should have text boxes for the min and max date values [txtMinDate] and [txtMaxDate]”

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

Hey dhookom, I put those 2 fields on the form, but it only brings up the form and doesn't display dialogue boxes for starting and ending dates.

Not sure what to do now.

 

[dhookom]

The code should be running in the form that has the Purpose, txtMinDate, and txtMaxDate controls on it. The form should be open so you can enter your criteria and click a button to open the report using the provided code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

Yes those fields are on that form and the form is open, but it is not working.

 

[dhookom]

but it is not working" doesn't tell us much. Does your code execute? Did you look into the immediate window in VBA? How to you trigger the opening of the report: a button or other event?

Please reply with your code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

Here is the code:

Private Sub Label79_Click()

Dim strWhere As String
strWhere = "[purpose] = """ & Me.purpose & """"
If Not IsNull(Me.txtMinDate) Then
    strWhere = strWhere & " AND [dateExp] >=#" & Me.txtMinDate & "# "
End If
If Not IsNull(Me.txtMaxDate) Then
    strWhere = strWhere & " AND [dateExp] <=#" & Me.txtMaxDate & "# "
End If
Debug.Print strWhere   'display value in the immediate window for debugging
DoCmd.OpenReport "rpt_Search", acViewPreview, , strWhere

End Sub

When I click the button to bring up the report, it does not ask me for a start date or an ending date. It just brings up the report.

 

[dhookom]

So, is "Label79" your button? You really need to implement a naming convention for all of your significant objects. You can troubleshoot by adding a breakpoint in the click event. This allows you to step through your code line by line to see what is happening. The Debug.Print displays something in your immediate window. What do you see there?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016