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

[dewaal99]

Hi There experts...

i am hoping that someone will be able to help me with a problem...

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.

i am using my table as my record source not a query.

thank you

DeWaal

[adam_k]

The criteria are being passed to your report as strWhere.
Copy this part of code:

If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

and change it from handling strdatefield & txtEndDate (along with < and isDate) to match your requirements.
You should end up with something similar to:

If Me.txtInst.text <> ''  Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strinstField & " = " & Me.txtInst.text & ")"
End If

PS: You'll need to declare and set value for the strinstField or what ever you choose to call the var that will hold the field name to compare your third criteria.