vba code to open an access report filtered between 2 dates

Question

Hi, I would very much appreciate if someone can give me the vba code  for opening a report in access, that is filtered between 2 dates.

I have created a Report named RepSurlinked to TableSur as its data source.

I have created a Form  namedFormSurin with  2 text boxes to select dates: DateFromand Dateto 

In the form I have a control that should open the report filtered by the 2 dates chosen in the textboxes

I know the vba code to open it to view it:

    DoCmd.OpenReport "RepSur", acViewReport

I don't know how to build the code for the where clause that I have to add to my vba code for the report to be filtered between the to dates.

What is the where clause that I have to add?

Thank you very much in advance for any help .  Best regards.

Answer 1

I note that your system short date format appears to be dd.mm.yyyy.  You might try using the ISO standard for data notation of YYYY-MM-DD.  This should work regardless of the regional date format settings of the system:

DoCmd.OpenReport ReportName:="RepSur", View:=acViewReport, _

        WhereCondition:="[DateField] Between #" & Format(Me.DateFrom, "yyyy-mm-dd") & _

        "# And #" & Format(Me.DateTo, "yyyy-mm-dd") &"#"

Answer 2

DoCmd.OpenReport ReportName:="RepSur", View:=acViewReport, _

        WhereCondition:="[DateField] Between #" & Format(Me.DateFrom, "mm/dd/yyyy") & _

        "# And #" & Format(Me.DateTo, "mm/dd/yyyy") &"#"

Answer 3

Dear Hans,

Thank you very much for your kind response. I tried your code but did not work. I got a runtime error 3075.

I am attaching some pictures for you to see how I created my table, form and report, so you can see what can be the problem. 

My textfields where I type the dates are formated  as shortdate with dd/mm/yyyy mask.

In my Report the field that I want to filter was named dia , which I changed to DateField, assuming that is the one that you put in your code.

After I got the error, I also changed your code  to see if that was the problem, but did not work either, and gave me the same 3075 runtime error

DoCmd.OpenReport ReportName:="RepSur", View:=acViewReport, _

        WhereCondition:="[DateField] Between #" & Format(Me.DateFrom, "dd/mm/yyyy") & _

        "# And #" & Format(Me.DateTo, "dd/mm/yyyy") &"#"

Please tell what is what I am doing wrong.

Answer 4

Hi Glenda, I'm an independent adviser and will try to help.

You have to add a WHERE clause to the code

DoCmd.OpenReport "RepSur", acViewReport, , "datefield BETWEEN #" & Me.DateFrom & "# AND #" & Me.DateTo & "#"

Or

DoCmd.OpenReport "RepSur", acViewReport, , "datefield >= #" & Me.DateFrom & "# AND datefield <= #" & Me.DateTo & "#"

Answer 5

You have to replace DateField with the actual name of your field: dia. Try this:

    DoCmd.OpenReport ReportName:="RepSur", View:=acViewReport, _

        WhereCondition:="[dia] Between #" & Format(Me.DateFrom, "mm/dd/yyyy") & _

        "# And #" & Format(Me.DateTo, "mm/dd/yyyy") &"#"