Using Date picker with aditional feild to run Report ... Please Help

dewaal99

New Member
Joined
Nov 3, 2011
Messages
22
Hi There Excel experts...

i am new to mr excel... and i am hoping that someone will be able to help me with a problem... it is not excel related but access 2007...

I have the following vba code.. some of you that are clued up with vba will know... it is a code i got from the net that allows me to choose dates before running my report...

Private Sub cmdPreview_Click()


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 = "Main View" 'Put your report name in these quotes.
strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
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

'Close the report if already open: otherwise it won't filter properly.
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

i have got a form that is not bound to any table with 2 text boxes first (txtStartDate) second (txtEndDate)... this allows me to put in a date from when to when my report should run.

i have put in a 3rd text box (inst) that is a combo box witch contains same info as the combo box in my form (inst1)...

i need someone to tweak my code a bit so that i could choose my dates and a feild in my combo box that will be equal to the fields in my form and run my report on the parameters entered.

i hope this is understandable... please if any info is required don't hesitate to ask. if anyone can help with this i will be saved a lot of work.

thank you

DeWaal
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dewaal99

New Member
Joined
Nov 3, 2011
Messages
22
thank you very much

i am pretty new to this, the date picking is all in order...

i just need to add another parameter to my existing code... this will help me allot...

dewaal
 

Xander117

Board Regular
Joined
Aug 3, 2011
Messages
66
Im confused as to what the combo box is for. Is that another element you wish to filter the report by?

Here is what I use for date range filtering. Its step by step and works great.

http://allenbrowne.com/casu-08.html

as far as using a combo box for a selector, I use something like this. I use it to select a specific report that I want to run without a filter.

Code:
Private Sub cmdnofilterrpt_Click()
 
    'run reports with no filters
    'selection is made from the cbonofilter combo box
 
 
    Dim stDocName As String
Let strDocName = Me![cbonofilter]
DoCmd.OpenReport strDocName, acPreview
Exit_Preview_Report_Click:
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top