Deebz
asked on
Reports from Crosstab Queries
This is a question I've been struggling with for over a year. The work-arounds I have in place are no longer working....
I have several crosstab queries where a batch date is designated as the Column Heading. The most current batch date is added and the oldest batchdate is removed daily.
Is there any possible way a report can be created from this data in such a way that the fields on the report don't have to be renamed daily?
I have several crosstab queries where a batch date is designated as the Column Heading. The most current batch date is added and the oldest batchdate is removed daily.
Is there any possible way a report can be created from this data in such a way that the fields on the report don't have to be renamed daily?
ASKER
I hate to do this to you, but could you post an example of this...? I have no access to the example mdb.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello Deeze and Jim
Jim, please correct me if I am wrong. I believe if you have Access on your machine, you should have the sample database.
Mine is locate here: C:\Program Files\Microsoft Office\Office\Samples\Solu
Jim, please correct me if I am wrong. I believe if you have Access on your machine, you should have the sample database.
Mine is locate here: C:\Program Files\Microsoft Office\Office\Samples\Solu
WonHop,
It's not installed by default. It may or may not be there. But if it's not, the install can be rerun.
Jim.
It's not installed by default. It may or may not be there. But if it's not, the install can be rerun.
Jim.
Ok...thanks Jim
Wonhop
Wonhop
ASKER
Jim...
just wanted to let you know that I'm meeting-impared today & won't be able to work with your code until tomorrow. Thanks for helping! I'll test it ASAP.
(btw...this is a company owned Access & if samples were installed, they were probably put on a one of those special out-of-the-way servers that only show up on the Network on the 5th Saturday of the 13th month.)
D
just wanted to let you know that I'm meeting-impared today & won't be able to work with your code until tomorrow. Thanks for helping! I'll test it ASAP.
(btw...this is a company owned Access & if samples were installed, they were probably put on a one of those special out-of-the-way servers that only show up on the Network on the 5th Saturday of the 13th month.)
D
ASKER
Jim...
I got a variable not defined for intColumnCount & dimmed it as an integer in report declarations (was this okay?)
Also, the code stops at Sub or Function not defined (("xtabCnulls")). This was used in the Sub Detail Format as
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
I'm not sure what to do with that to get farther into the code...
I got a variable not defined for intColumnCount & dimmed it as an integer in report declarations (was this okay?)
Also, the code stops at Sub or Function not defined (("xtabCnulls")). This was used in the Sub Detail Format as
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
I'm not sure what to do with that to get farther into the code...
Sorry.
Yes intColumnCout should be an integer.
The xTabCNulls is just a null to zero function. You can use NZ() in it's place or drop it.
Also be aware that I adjusted the code to eliminate the totals column that was in the sample, so I might be off a bit. Give it a whirl and let me know.
Jim.
Yes intColumnCout should be an integer.
The xTabCNulls is just a null to zero function. You can use NZ() in it's place or drop it.
Also be aware that I adjusted the code to eliminate the totals column that was in the sample, so I might be off a bit. Give it a whirl and let me know.
Jim.
ASKER
Jim...The report looks like it set up okay, but for some reason I'm only getting one record. Any idea why?
First, make sure that the query your using is returning more then one record and that the report is using it for its data source.
Then check and make sure that your doing this:
' Move to next record in recordset.
rstReport.MoveNext
in the detail section's OnFormat event.
Jim.
Then check and make sure that your doing this:
' Move to next record in recordset.
rstReport.MoveNext
in the detail section's OnFormat event.
Jim.
ASKER
my record source is okay and I'm using the correct data source. and I do have rstReport.MoveNext in the detail section's OnFormat event. I also took out all the sorts, but that didn't help. Could it have something to do with this line:
Me("Col" + Format(intX)) = Nz(rstReport(intX - 1))
This is the line that I replaced the xtabCnulls with the Nz function.
Thanks again :)
Deb
Me("Col" + Format(intX)) = Nz(rstReport(intX - 1))
This is the line that I replaced the xtabCnulls with the Nz function.
Thanks again :)
Deb
I guess it depends what you mean by "only one record". Same record repeated over and over or simply one detail line?
But in either case, the NZ would not make a difference. Below is the original function and as you can see, it simply sets the value to a 0 if the field is null, otherwise it gives you the field value.
Jim
Private Function xtabCnulls(varX As Variant)
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
But in either case, the NZ would not make a difference. Below is the original function and as you can see, it simply sets the value to a 0 if the field is null, otherwise it gives you the field value.
Jim
Private Function xtabCnulls(varX As Variant)
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
ASKER
I'm getting only the first record of the set. Still trying to figure out why I can't get more...
Send along a MDB if you can...
Jim.
jimdettman@earthlink.net
Jim.
jimdettman@earthlink.net
What it amounts to is that you fill the columns on the fly in the detail sections OnFormat event and the reports/group heading sections OnFormat event.
I can walk you through this step by step if need be.
Jim.