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

[dewaal99]

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

 

[jackd]

These tutorials may be helpful

http://www.fontstuff.com/access/acctut13a.htm

or

http://www.datapigtechnologies.com/flashfiles/clndrcontrol.html

 

[dewaal99]

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]

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.

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