Dates in a Query (1 Viewer)

[sara82]

I have a report that once clicked a form comes up to choose the dates one would like to filter by. It has a Start Date and End Date. After the user picks the Start Date and End Date the report will be shown for those dates. The record source for this report is a query

I wanted to use this same date picker form for another report. I included the same expression into my query but I keep getting an error.

Field: Expr2: [LogDate] Between [forms]![frmWhatDatesWorkLog].[txtRecvStart] And [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Or [forms]![frmWhatDatesWorkLog].[txtRecvStart] Is Null Or [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Is Null

Criteria: True

When I tried that I get the following error: "The Microsoft Jet database engine does not recognize '[forms]![frmWhatDatesWorkLog].[txtRecvStart]' as a valid field name or expression.

I don't know why I get this because I use the exact same thing in the other query and it works perfectly fine.

Any suggestions?

 

[Rovers]

I would avoid using the same form (pop up parameter window) to invoke two different reports. Copy the form and call it something else and make sure all the code is referencing the right report and the text boxes are named correctly... all the normal things.
__

Rather than using the Null checker in your criteria I would use an "input mask" through the properties of the "txtRecvStart" and "txtRecvEnd " text boxes and give a default value so it will never be Null. This makes it less likely for the user to input incorrectly.

 

[sara82]

Rovers,
I tried your suggested but I get the same error message.

[forms]![frmDate].[txtStart]' as a valid field name or expression.

 

[sara82]

The query I have is a Select query but it has some fields that are based on a crosstab query so I am guessing that may have something to do with the problem?

 

[Rich]

You need to define the Parameters as Date/Time for your crosstab query

 

[sara82]

Rich,
Sorry I'm not getting this right way, but where do I sent the parameters to Date/Time in the crosstab query?

I have a select query which has fields from 2 Cross Tab Queries. These queries find sums of hours and total pages employees have worked on.

I have the field "Log Date" but if I include the Log Date field in my select query it will show each entry for the date. And I don't want that because I'm finding sums.

But for my report I want to be able to pull Employee sums for each Month. And I just don't know how to go about doing that.

 

[sara82]

Below is a visual picture of what's going on:

Two crosstab Queries one that calcuates the Sum of Total Hours employees worked for each Job Type and the other calculates Sum of the Total Pages worked for each Job Type

Then I have a select query that inclueds both the Sum of Total Hours and Some of Total Pages so that I can view all of this information in a report.

My report is based on the select query (last pic)

I need to filter this report by Month and Year. Where the user can choose the month anda year and it'll show the result for that criteria. I tried with the date picker form but I keep getting the error I noted above