MS Access - Docmd.OpenReport

1
votes

I'm trying to open a report with date field between two dates (dateFrom & dateTo) which comes from the textboxes in a form. 

dateFrom = Forms!formOptions!txtDateFrom.Value
dateTo = Forms!formOptions!txtDateTo.Value

Have tried:

(Edit:)

with and without the '#' around the dateFrom and dateTo variables

& with and without the [] around the field name, and still cannot get this to work.

When I try this code: 

strCriteria = "[ActionTime] >= '" & dateFrom & "' And [ActionTime] <= '" & dateTo & "'"
DoCmd.OpenReport "reportLog", acViewReport, , strCriteria

An 'Enter a parameter value' dialog will popup, I've checked the control source of the report & the field name is correct & there are no sorting or grouping happening either.

And when I put a pair of extra ' ' around the field name in the above code to be: 

strCriteria = "'[ActionTime]' >= '" & dateFrom & "' And '[ActionTime]' <= '" & dateTo & "'"
DoCmd.OpenReport "reportLog", acViewReport, , strCriteria

The report opens normally but instead of just the record between the dates, all of the records appear.

Have also tried using the WhereCondition:

DoCmd.OpenReport "reportLog", acViewReport, , WhereCondition:="'[ActionTime] >= #" & dateFrom & "# AND [ActionTime] <= #" & dateTo & "#'"

And the report opens normally but with all the records, not following the WhereCondition.

I don't know which part of this coding is false, have referred to other sites but still can't detect what's exactly wrong. Would be grateful for any guidance. Thanks in advance.

2
ms-accessvba

2 Answers

1
votes

You need properly formatted string expressions for your date values:

Dim dateFrom As Date
Dim dateTo As Date

dateFrom = DateValue(Forms!formOptions!txtDateFrom.Value)
dateTo = DateValue(Forms!formOptions!txtDateTo.Value)

strCriteria = "[ActionTime] >= #" & Format(dateFrom, "yyyy\/mm\/dd") & "# And [ActionTime] <= #" & Format(dateTo, "yyyy\/mm\/dd") & "#"
Debug.Print "'" & strCriteria & "'"
DoCmd.OpenReport "reportLog", acViewReport, , strCriteria

Edit: If ActionTime appears more than once, try:

strCriteria = "[reportLog].[ActionTime] >= #" & Format(dateFrom, "yyyy\/mm\/dd") & "# And [reportLog].[ActionTime] <= #" & Format(dateTo, "yyyy\/mm\/dd") & "#"
2
votes

The shared attempts won't work. The first, because dates are supposed to be formatted in a specific way (YYYY-MM-DD or MM/DD/YYYY) and surrounded by # (the value, not the field name). The second, because you're comparing the literal string '[ActionTime]' to a string containing a date, and that's not a very sensible thing to do.

However, the easy and safe way to do this is just to set parameters:

strCriteria = "[ActionTime] >= dateFrom And [ActionTime] <=  dateTo"
DoCmd.SetParameter "dateFrom", dateFrom
DoCmd.SetParameter "dateTo", dateTo
DoCmd.OpenReport "reportLog", acViewReport, , strCriteria

Read more about using parameters here.