open report with parameter

[xraive]

I am trying to pass the parameters when I open a report but the report opens with all records instead of being filtered.

Dim strReport As String
    Dim strDateField As String
    Dim startDate As String
    Dim endDate As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
 
       strDateField=[SubmitDate]
    Select Case Me!ReportToPrint
 
    Case 3
            If IsNull(Forms![Reports Dialog]!SelectCategory) Then
                DoCmd.OpenReport "Category Reports", PrintMode
            Else
 
     If SelectCategory.ListIndex = 0 Then
        startDate = Date - 2
        MsgBox startDate
        endDate = Date
 
 
    End If
        MsgBox strDateField
         strWhere = "(" & strDateField & " < " & Format(startDate, strcJetDate) & ")"
        strWhere = strWhere & "(" & strDateField & " < " & endDate & ")"
        Debug.Print strWhere
        DoCmd.OpenReport "Category Reports", PrintMode, , strWhere
            End If
    End Select
    DoCmd.Close acForm, "Reports Dialog"

[FishVal]

Hello, xraive.

Your code is very messy.

• startDate and endDate are declared as String type variables. However you assign Date type values to them. Though VBA could silently convert type to an appropriate one, the resulting value could be what you are not expecting for. To say nothing about that Format() function can't format String type value as Date type value.
• Even though you've not attempted to format properly endDate variable value while incorporating it to strWhere.
• There is no logical operator between two conditions in strWhere.
• Having
  Debug.Print strWhere
  you might pay attention to the value of strWhere you try to pass to the report.
• Just out of curiosity - what is the reason to name vaiables startDate and endDate this way. Do you think it is something about Hungarian notation?

Kind regards,
Fish.

[NeoPa]

XRaive,

Please remember to use the CODE tags rather than the QUOTE tags for your code.

Also, it is courteous to indent your code properly before posting if you expect people to read through it. Messy code is no help to you, and is likely to irritate those you are hoping will help you.

If Fish's comments don't help you to your solution (I expect they will in fact) then I suggest you get clear which line of code is causing your problem (which of the two calls on lines #14 & #28) and then look through the Help documentation for the call (DoCmd.OpenReport()). You will find everything you need there to explain your problem.

[xraive]

I apologize for the messy code guys and for not using the code tags. I will in the future.

Thank you,

[NeoPa]

Did you find that sorted your problem?

PS. Some (hopefully) helpful info on using the CODE tags (I meant to post before for you) :

Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).

[xraive]

Thank you Neopa I will follow your instructions.

In regards to the problem I was having I decided to create hidden text fields that store date criteria on the form. I changed the query to refrence those two date fields on the form when running the report. This way I removed the need to store the date values in my code.

Thank you,

Rezene A.

[NeoPa]

That sounds good. As long as your problem is behind you :)