1

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.

orionzelda
  • 25
  • 2
  • 7

2 Answers2

2

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • what I meant was that I tried with and without the # around the dateFrom & dateTo variables. And also with and without the [] around the field name. Will edit the question. – orionzelda May 15 '18 at 09:53
  • But ActionTime in this report is taken from a table, and in the table, the type for this field is date, is it still considered as string? I'll try the method you've recommended, thanks. – orionzelda May 15 '18 at 09:58
  • Anything delimited by `'` quotes is a string in Access SQL. There's very little automatic typecasting. – Erik A May 15 '18 at 10:17
  • Thank you for your time & suggestion. – orionzelda May 23 '18 at 08:00
1

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") & "#"
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Tried this, but the 'Enter parameter value' dialog is still popping-up. I thought perhaps because it can't recognize [ActionTime], and added [reportLog]! in front of the [ActionTime] to be like [reportLog]![ActionTime], this time the dialog did not pop-up and the report opens, but it still doesn't follow the date criteria. Thanks for your suggestion. – orionzelda May 21 '18 at 01:45
  • Then `[ActionTime]` is misspelled or isn't a field in the recordsource of the report. – Gustav May 21 '18 at 10:06
  • I've checked multiple times and `[ActionTime]` is the exact same name of the field in both the report and also the table, and yes the spelling is the same. I don't know what else is causing this. – orionzelda May 22 '18 at 07:54
  • Check the criteria string - see Debug.Print in the edited answer. – Gustav May 22 '18 at 11:27
  • I've also checked using debug before. And this is what comes up in the immediate window. `[ActionTime] >= #05/22/2018# AND [ActionTime] <= #05/23/2018#.` I formatted the date as mm/dd/yyyy as that's what the date format is for the field `[ActionTime]`. – orionzelda May 23 '18 at 01:53
  • A date value carries no format. The purpose of Format is to create a string expression for a date value that will always be understood correctly by Access SQL. But I noticed your first comment, so see edited answer, please. – Gustav May 23 '18 at 07:28
  • I deleted the report, and made another one and also re-wrote the code, following what you advised. And it seems to work. I don't know why it didn't before. So, I'll accept this as an answer. Thank you for your time. – orionzelda May 23 '18 at 07:59