Dates in a Query 1

[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 includes 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 am unsure how I would do that.

I tried to enter a parameter and even a date picker form but I keep getting errors I'm guessing it has something to do with the cross tab fields?

 

[SkipVought]

sara82,

Where is the DATE FIELD in your table(s)????????????

I absolutey KNOW that it is impossible to select on date values is there is no date field!

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[sara82]

SkipVought:

When I include the date field it messes up my sums. It'll include each record for each date and the values of the sums becomes wrong.

 

[PHV]

Include the date field in the field cell, untick the corresponding Show cell and populate the Criteria cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

PHV:

I added the date field and Unchecked the Show.

In the Criteria I entered: Between #10/1/2005# And #10/31/2005#

But that means for each month I will need to change the criteria?

 

[PHV]

Have a look at PARAMETERS.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

I have tried parameters plus a Date picker form but when I do I get the error that the microsoft jet database engine does not recognize a vaild field name or expression.

Field: Expr2: [LogDate] Between [forms]![frmDate].[txtStart] And [forms]![frmDate].[txtEnd] Or [forms]![frmDate].[txtStart] Is Null Or [forms]![frmDate].[txtEnd] Is Null

Criteria: True

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

My forms and fields are all named correctly

 

[PHV]

Field: LogDate
Show: Unticked
Criteria: Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And Nz([Forms]![frmDate]![txtEnd],[LogDate])

The frmDate forms must be open when the query is launched.
Define the 2 parameters as DateTime in this query and in any crosstab query referencing this query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

I followed through your instructions, sorry but bear with me with the questions.

Field: LogDate
Table: tblWorkLog
Show: Unticked
Criteria: Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And Nz([Forms]![frmDate]![txtEnd],[LogDate])

I have frmDate form Open when I run the query I get the error: "The Microsoft Jet database engine does not recognize '[forms]![frmDate].[txtStart]' as a valid field name or expression.

When you say "Define the 2 parameters as DateTime in this query and in any crosstab query referencing this query. "

Which two parameters are you talking about. The 2 fields on the form are formatted as ShorDate.

 

[sara82]

Sorry it took me a while. Now I understand where you said to set the parameters. I set them as:

[Forms]![frmDate]![txtStart] Date/Time
[Forms]![frmDate]![txtEnd] Date/Time

Now I am not getting the error message anymore but it's not filtering according to the dates. I will play around with it and see if I can figure it out.

 

[sara82]

I think I may have solved the problem. I'm still testing it out to make sure.
The Log Date was storing both the Date and Time. LogDate is being populated by (Now) in the form's Before Insert Event.

I had: Me.LogDate = (Now)

I changed it to Me.LogDate = Format(Now, "mmm d yyyy") So that only the date will get stored.

Once I did that it allowed me to filter b/w my start and end dates and it included everything in that range.

Thanks PHV for all of your help. I really appreciate it.

 

[4281967]

sara82,
Off Topic but how did you post those images?

 

[sara82]

I have my pictures uploaded on photobucket.com And then I referece my picture: