12 month running total w/mid month import

[kentwoodjean]

Have a crosstab with criteria for most recent 12 months. Here is the parameter information:

Between (DateSerial((Year(Now())-1),Month(Now()),1)) And (DateSerial((Year(Now())),IIf(Month(Now())=1,12,Month(Now())-0),1))

I need to make a slight change to accomodate the fact that my data does not arrive until about the 15th of the following month (I.e. June data will come about 7/15). My user puts in the date parameter that they are looking for and we want to see that date/year on the report so I am not just using month (I.e. May, June,) for my field name.
Right now when I run the query, I get 07/04 - 05/05 data.

What minior adjustment might I need to do with the above to allow me to retrieve the data by year-month? I tried several different changes, and I am not getting it right. Thanks.

 

[dhookom]

See this FAQ on dynamic monthly crosstab query reports faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Sorry I did not get back to you sooner and thank you for your input. I read the FAQ several times to understand how I would format. It is always easier to figure out when you are using your own "Fields". In the meantime, my user has now requested that instead of a rolling 12 months, they would like to have a parameter set requesting the "Begin Date' and "End Date" that would cover a 12 month period.

Thinking that it is a bit different than the rolling, I tried something a bit different. You helped me previously with a Union query and we did put criteria on that. Therefore, I thought I could put the parameteres there as well. Have tried several different sets of parenthesis etc and it is not working. Looking at what I have done on the Union query can you advise what I am doing wrong.

I have a crosstab that was made as a result of this Union query (Date (Workdate)is a column heading.

SELECT EMPID, SECTID, DATE as WorkDate, [Desc] as Description, Lost as Hrs, "Lost" as Category
FROM [WMI LNN EMP DETAIL 92660]
WHERE ([Lost]) Is not Null
WHERE ( [DATE]) Between [Begin Date] And [End Date]
UNION ALL
SELECT EMPID,SECTID, DATE, [Desc], [Non-Prod], "NonProd"
FROM [WMI LNN EMP DETAIL 92660]
WHERE ([Non-Prod]) Is not Null
WHERE ( [DATE]) Between [Begin Date] And [End Date]
UNION ALL
SELECT EMPID, SECTID,DATE, [Desc], [Production], "Production"
FROM [WMI LNN EMP DETAIL 92660]
WHERE ([Production]) Is not Null
WHERE ( [DATE]) Between [Begin Date] And [End Date]
UNION ALL SELECT EMPID, SECTID,Date, [DESC], [Efficiency], "Efficiency"
FROM [WMI_ EMP_PERF by Operation]
WHERE ([Efficiency]) Is not Null
WHERE ( [DATE]) Between [Begin Date] And [End Date];

 

[dhookom]

This doesn't make sense

they would like to have a parameter set requesting the "Begin Date' and "End Date" that would cover a 12 month period.

If the time period is 12 months, you should only need a beginning or an ending date, not both.

The faq that I suggested would handle this quite well without any date range in the Union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]