Limiting date ranges & aggregated query

[Forbes]

Hey guys, so we have a few reports that use ConcatRelated to fill data in a column on the report we are printing. We had to add a way to limit date ranges for the report instead of just printing reports for that month (time=NOW). I have followed the second instructional guide on here http://allenbrowne.com/casu-08.html I have filled in the script with my names and everything and when I press the button the report comes up but I get a "#Type!" in the concat related field and the rest are blank

ConcatRelated:

=ConcatRelated("[condname]","reports","[cond] = '" & [cond] & "' And ([condemp]='Bree' Or [condemp]='Ben' Or [condemp]='Plumb' Or [condemp]='Lisa' Or [condemp]='Shaver' Or [condemp]='Smith' Or [condemp]='Julie')")

I know the guide says that if you have a query that is behind the report as well it may not filter correctly, but I must find a way.

[Forbes]

When I attempt the first step however the dialog boxes show but the reports still pulls up blank after entering the dates.

((([reports].[timestamp])>=[StartDate])<[EndDate]+1))

[ranman256]

Put the 2 dates in a form for the query to read....

Reports.timestamp between forms!myForm!txtStartdate and forms!myform!txtEndDate

[Forbes]

Query

 (((reports.timestamp)) Between [Forms]![reportprint]![txtStart] And [Forms]![reportprint]![txtEnd]))

^SQL View
Form

=[Forms]![reportprint]![txtStart]
=[Forms]![reportprint]![txtEnd]

^Formatted to Short Date

Report

=[Forms]![reportprint]![txtStart]
=[Forms]![reportprint]![txtEnd]

^Formatted to Short Date

I have the text boxes on the report and the form properly and removed the parameter prompt, however the text boxes in the form show #Error

[pbaldy]

You're saying the ConcatRelated function is returning the error? If the recordset in that function is being opened on the query, it can't resolve the form references. The simplest workaround is the Eval() function. Try

reports.timestamp Between Eval("[Forms]![reportprint]![txtStart]") And Eval("[Forms]![reportprint]![txtEnd]")

[Forbes]

The error# is in the text boxes on the form itself in form view. I am trying this eval() now

[Forbes]

SELECT reports.cond, Count(reports.cond) AS CountOfcond
FROM reports
WHERE (((reports.condemp)="name" Or (reports.condemp)="name" Or (reports.condemp)="name" Or (reports.condemp)="name" Or (reports.condemp)="name" Or (reports.condemp)="name" Or (reports.condemp)="name") And (((reports.timestamp)) Between Eval("[Forms]![reportprint]![txtStart]") And Eval("[Forms]![reportprint]![txtEnd]")))
GROUP BY reports.cond;

Now the report prints blank with this

[pbaldy]

Did you use the actual names instead of "name"? If it printed appropriate records without the Eval() function, adding it shouldn't have changed what displayed on the report, just how the function used the query.

[Forbes]

Yeah, I have to remove the names for privacy, when I click the button to bring up the report I get "Data type mismatch in criteria expression" followed by:

Attachment 28035

Then when I click the report again it appears blank.

[pbaldy]

Yeah, I have to remove the names for privacy

Better remove them from the first post then.

So the query and report work without the Eval() function (except for the ConcatRelated textbox), and adding it throws the error?

[Forbes]

Haha the first post is fictitious but I appreciate the lookout haha.

I started with this
(((reports.timestamp)) Between [Forms]![reportprint]![txtStart] And [Forms]![reportprint]![txtEnd]))
but the report would just come up with no data

then with
(((reports.timestamp)) Between Eval("[Forms]![reportprint]![txtStart]") And Eval("[Forms]![reportprint]![txtEnd]")))
it sends me the error, honestly I don't think any of the issues are related to concat but I just wanted to throw that information in the question in case, I know it has been giving us a lot of problems in the past.

[Forbes]

AND (reports.timestamp Between Eval("[Forms]![reportprint]![txtStart]") And Eval("[Forms]![reportprint]![txtEnd]") ))
GROUP BY reports.cond;

I changed the data format on my database to match the short date so no more mismatch... however I still show ERROR# in the text boxes on the form

=[Forms]![reportprint]![txtStart]
=[Forms]![reportprint]![txtEnd]

[pbaldy]

The form is open? Can you attach the db here?

[Forbes]

I can, I will need to do it tomorrow and the names conversion will take some time, so around tomorrow afternoon.

[Forbes]

Even after attempting to zip the file, the upload still will not take. Could you possibly pm me a junk email of yours I can send the compressed file too? Or offer a solution to my upload problems haha.