Using Crosstab Query for Report

[tmyers]

@Pat Hartman How did you handle columns that did not end up with a value?

 

[tmyers]

The crosstab adjust itself correctly, but I am having an issue with the report. I made the report with 7 total columns since it would never go over that, but when I use less than that, it throws an error - Run-time error "3070": The Microsoft Access database engine does not recognize " as a valid field name or expression.

So I think that is happening is the on load event renames the unused columns as " or something, which Access doesnt like it.

EDIT:
Never mind. The error happens before the on load event even occurs.

 

[tmyers]

I think the problem lies here:

The columns that weren't used are changed to that expression. That is interesting.

 

[theDBguy]

The columns that weren't used are changed to that expression. That is interesting.

I could be wrong, but I think that only happens if you open the query in design view when the source data is not available. Otherwise, if you left the query alone, you could still get a prompt, but it won't (shouldn't) be the generic Expr1 type.

 

[tmyers]

I think the problem lies in the fact that if the crosstab changes and the column no longer exist, it is putting "" (empty string) as the column header, which Access really doesn't like. How could I add in IF ISNULL to this so rather than an empty string, its just 0? I could then set visibility to false for controls whos caption = 0

 

[Gasman]

I think the problem lies in the fact that if the crosstab changes and the column no longer exist, it is putting "" (empty string) as the column header, which Access really doesn't like. How could I add in IF ISNULL to this so rather than an empty string, its just 0? I could then set visibility to false for controls whos caption = 0

You should only be supplying columns names for columns that have data, if you do not want to see empty columns.?
So create your PIVOT statement from known data that populates the columns?

 

[tmyers]

What would that PIVOT statement look like? Currently my PIVOT for the crosstab is: PIVOT TEMP_AssignSequence.SeqNum;
After testing small pieces of it, I think the problem lies within the report. Im not currently sure how to fix it but am going to keep tinkering with it.

 

[Gasman]

What would that PIVOT statement look like? Currently my PIVOT for the crosstab is: PIVOT TEMP_AssignSequence.SeqNum;

The same as we did for the other crosstab.?
In mine I searched for active users, you searched for contractors.?

 

[tmyers]

The same as we did for the other crosstab.?
In mine I searched for active users, you searched for contractors.?

Derp. Its early and I need more coffee.

 

[tmyers]

I fixed the query to not return those empty strings anymore, but now when I try to open the report, is get error (-3007) and it says there is no message for this error.

I assume it is the report not agreeing with the fact some columns present on the report, are no longer present in its record source which is the query.

 

[Gasman]

Check out this link. http://www.rogersaccesslibrary.com/...a1e7c9cb-2fc9-a8ce2929-1ab7a125-zd3zb9b1.html
I did not progress to making such a report as my users were static pretty much.

 

[tmyers]

I am unable to open that sadly as I am running 2016 with no access (no pun intended) to earlier versions.

 

[Gasman]

I converted it to 2007

 

[tmyers]

It works! I think I can finally check this off as complete.

 

[Gasman]

tmyers, Excellent.

Gasman, Since Access no longer supports A97 I can't even look at the example. I don't know if Roger is maintaining his library or if someone else has taken it over. Do you know?

Pat,
I would not know that. I just did a quick Google for a way.
I converted that DB to 2007 for the O/P. Probably one of the few benefits left to using 2007.
This DB was by someone called Duane Hookom

FWIW if anyone ever needs a MDB converting and are unable due to having a version of Access that objects to converting it, they only need to upload and ask, or PM me.

 

[Gasman]

@abdullahkhan Re your PM about the code in post #3

gCallSystem is a global variable (hence the g) and appears to be a string value as I surrounded it in quotes.?

It would hold the name of the call recording system I wanted to report on, or be blank if I wanted to report on all.?

It would be set just before calling this code?, probably from a combo.?

That DB is not on this computer, so I would need to go to the works computer if this is still not clear.?

HTH