Crosstab query acting up..?

[snipe]

Hey guys,



I have attached a stripped down version of our bonus program. The "Dots report" has decided to start acting up for some reason.

This report is every salespersons warranty sales percentage, and conditional formatting color codes it. The user selects the desired quarter on the main form and hits the button. The report is a crosstab with the past weeks being the column heading, which is all pulled from the DOTS table. In that table, you will see that there is data all the way up to 4/13/14 (Date To field in DOTS table). I cannot figure out why the most recent week (week ending 4/13/14) is not showing up in this report (or the crosstab query for that matter).

It has worked great since inception which was around November last year.

Also, not sure if this info is needed:
-VBA prompts the user every Monday if they want to add the last weeks data
-This code is in the On Load event of the form
-All data in the DOTS table is in 1 week blocks

[rpeare]

What are you trying to do with the week calculation in your crosstab? There are all kinds of faulty issues (at least in my view) what date related functions in access.

For instance if you do a datediff("yyyy", firstdate, seconddate)

let's say the firstdate is 4/1/2013 and the second date is 2/1/2014 this would be calculated as 1 year even though it's not technically a year because it crosses the 12/31 date.

So depending on what you're actually trying to get at with this code is where I'd start looking for the problem.

[snipe]

The week calculation is basically just showing 1 quarter of the year (broken down by weeks) for salespeople's performance. The datediff equation is just to count out the number of weeks. I got most of the code from: http://www.access.hookom.net/Dynamic...rosstabRpt.htm

The report fills in the weeks, starting from the left, as the quarter goes on. This report is ran every Monday and sent out to the managers. Everything has acted exactly as it should up until this last week which is what is confusing me so much.

For instance if you do a datediff("yyyy", firstdate, seconddate)

let's say the firstdate is 4/1/2013 and the second date is 2/1/2014 this would be calculated as 1 year even though it's not technically a year because it crosses the 12/31 date.

I've read about these issues as well. The way my DOTS table is set up combined with my VBA forcing the dates to be a Sunday thru Monday have pretty much fixed any issues that I could find.

[rpeare]

Look at this query

SELECT DOTS.[Date From], DOTS.[Date To], DOTS.SO_STORE_CD, DOTS.Name, DOTS.Pct, DOTS.Covered, DOTS.Total, DOTS.EmpCode, MISC_EMP.TITLE
FROM DOTS LEFT JOIN MISC_EMP ON DOTS.EmpCode = MISC_EMP.EMP_CD;

The only criteria in your crosstab is that the TITLE must be "SALES" or "AMGR"

Note that for the 4/13 ALL of your EMPCODES in your DOTS table are null, which in turn means you will have all null values in your TITLE field so your crosstab isn't pulling any data. In order to capture the null values you'd have to have

"SALES" or "AMGR" or Is Null as your critiera but this might give you results you don't want either.

[snipe]

Note that for the 4/13 ALL of your EMPCODES in your DOTS table are null

That's it. God. How did I not catch that?? Those should be populating from the append query. Easy fix.

Thank you so much!