0

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

Edited by Dani: Formatting fixed

2
Contributors
1
Reply
4
Views
5 Years
Discussion Span
Last Post by adam_k
0

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.

Edited by adam_k: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.