Dynamic Reporting based on Parameterized Crosstab Query

Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Josiah" wrote in message
...
Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.

Since the column headings are all dates at a regular interval, I would use
the solution for dynamic monthly crosstab at
http://www.tek-tips.com/faqs.cfm?fid=5466. You can easily change the month
interval to day. I don't think this solution involved any code.

--
Duane Hookom
Microsoft Access MVP


"Roger Carlson" wrote:

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Josiah" wrote in message
...
Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.

On Aug 7, 2:11*pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. *You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
* MS Access MVP
* Access Database Samples:www.rogersaccesslibrary.com
* Want answers to your Access questions in your Email?
* Free subscription:
*http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

...



Elements and Structu *I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. *This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. *Then, the columnwise field is based on the labor
charge date charged hours are associated with. *The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. *These 2 parameters are used to
determine the date range to pull labor charging information from. *For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. *The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). *The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: *What I want to do is create a report based on this query. *I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. *If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. *I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: *since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. *That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. *I have all of the labels successfully displaying
the correct field names in the form. *However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. *The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. *I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. *So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. *This works fine. *But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

Since the column headings are all dates at a regular interval, I would use
the solution for dynamic monthly crosstab at
http://www.tek-tips.com/faqs.cfm?fid=5466. You can easily change the month
interval to day. I don't think this solution involved any code.

--
Duane Hookom
Microsoft Access MVP

I'll look at these 2 solutions and see which one, if any, suits my
needs. I've already looked some at Roger's solution, and I think that
will work. I will get back to you both and update you on my progress
sometime on Monday when I get back to work. Thanks a ton for the
help.

-Josiah

On Aug 7, 2:11*pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. *You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
* MS Access MVP
* Access Database Samples:www.rogersaccesslibrary.com
* Want answers to your Access questions in your Email?
* Free subscription:
*http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

...



Elements and Structu *I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. *This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. *Then, the columnwise field is based on the labor
charge date charged hours are associated with. *The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. *These 2 parameters are used to
determine the date range to pull labor charging information from. *For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. *The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). *The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: *What I want to do is create a report based on this query. *I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. *If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. *I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: *since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. *That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. *I have all of the labels successfully displaying
the correct field names in the form. *However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. *The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. *I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. *So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. *This works fine. *But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). I think it is because the query cant access the fields in
my crosstab. is there any way around this? Am I right in my
conjectures, even?

Josiah,
Did you check out my suggested solution from Tek-Tips? It doesn't require
any code.

--
Duane Hookom
Microsoft Access MVP


"Josiah" wrote:

On Aug 7, 2:11 pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

...



Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?.

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). I think it is because the query cant access the fields in
my crosstab. is there any way around this? Am I right in my
conjectures, even?

On Aug 10, 1:51*pm, Duane Hookom
wrote:
Josiah,
Did you check out my suggested solution from Tek-Tips? It doesn't require
any code.

--
Duane Hookom
Microsoft Access MVP



"Josiah" wrote:
On Aug 7, 2:11 pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. *You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
* MS Access MVP
* Access Database Samples:www.rogersaccesslibrary.com
* Want answers to your Access questions in your Email?
* Free subscription:
*http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

....

Elements and Structu *I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. *This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. *Then, the columnwise field is based on the labor
charge date charged hours are associated with. *The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. *These 2 parameters are used to
determine the date range to pull labor charging information from. *For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. *The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). *The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: *What I want to do is create a report based on this query. *I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. *If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. *I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: *since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. *That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. *I have all of the labels successfully displaying
the correct field names in the form. *However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. *The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. *I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. *So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. *This works fine. *But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?..

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. *I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. *For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. *If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). *I think it is because the query cant access the fields in
my crosstab. *is there any way around this? *Am I right in my
conjectures, even?- Hide quoted text -

- Show quoted text -

I need to be able to display the actual date on the report, I cant do
the relative headings. Sorry, I would like taht solution, too, but it
doesnt meet the business needs here.

"I need to be able to display the actual date on the report"
The tek-tips solution does display the actual date on the report.

What business needs doesn't it meet?

--
Duane Hookom
Microsoft Access MVP


"Josiah" wrote:

On Aug 10, 1:51 pm, Duane Hookom
wrote:
Josiah,
Did you check out my suggested solution from Tek-Tips? It doesn't require
any code.

--
Duane Hookom
Microsoft Access MVP



"Josiah" wrote:
On Aug 7, 2:11 pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

....

Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?..

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). I think it is because the query cant access the fields in
my crosstab. is there any way around this? Am I right in my
conjectures, even?- Hide quoted text -

- Show quoted text -

I need to be able to display the actual date on the report, I cant do
the relative headings. Sorry, I would like taht solution, too, but it
doesnt meet the business needs here.

On Aug 10, 6:46*pm, Duane Hookom
wrote:
"I need to be able to display the actual date on the report"
The tek-tips solution does display the actual date on the report.

What business needs doesn't it meet?

--
Duane Hookom
Microsoft Access MVP



"Josiah" wrote:
On Aug 10, 1:51 pm, Duane Hookom
wrote:
Josiah,
Did you check out my suggested solution from Tek-Tips? It doesn't require
any code.

--
Duane Hookom
Microsoft Access MVP

"Josiah" wrote:
On Aug 7, 2:11 pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. *You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
* MS Access MVP
* Access Database Samples:www.rogersaccesslibrary.com
* Want answers to your Access questions in your Email?
* Free subscription:
*http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

....

Elements and Structu *I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. *This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. *Then, the columnwise field is based on the labor
charge date charged hours are associated with. *The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. *These 2 parameters are used to
determine the date range to pull labor charging information from. *For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. *The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). *The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: *What I want to do is create a report based on this query. *I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. *If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. *I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: *since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. *That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. *I have all of the labels successfully displaying
the correct field names in the form. *However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. *The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. *I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. *So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. *This works fine. *But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?..

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. *I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. *For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. *If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). *I think it is because the query cant access the fields in
my crosstab. *is there any way around this? *Am I right in my
conjectures, even?- Hide quoted text -

- Show quoted text -

I need to be able to display the actual date on the report, I cant do
the relative headings. *Sorry, I would like taht solution, too, but it
doesnt meet the business needs here.- Hide quoted text -

- Show quoted text -

The link you sent me replaces the dates with Mth0-MthN. I can't have
that, I need to be able to display the date that's comming out of the
database because its all based on the financial labor charge date.
Those viewing the reports need the 'real date', according to
management.


A progress note, I have determined that in using a QueryDef to access
my crosstab query in VBA, the reason I cant see the fields is for sure
linked to my use of the 'WHERE Labor_Charge_date BETWEEN [forms!myForm!
cboFromDate.Value] AND [forms!myForm!cboToDate.Value]' phrase. I
recently created a copy of the database where I just always query on
the past 6 weeks of financial data and now in the VBA, when I create a
QueryDef of my crosstab, I can see all my fields when i 'watch' my
QueryDef.

My solution in Tek-Tips does show the real date values in the report column
headings. Review this again at http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


"Josiah" wrote:

On Aug 10, 6:46 pm, Duane Hookom
wrote:
"I need to be able to display the actual date on the report"
The tek-tips solution does display the actual date on the report.

What business needs doesn't it meet?

--
Duane Hookom
Microsoft Access MVP



"Josiah" wrote:
On Aug 10, 1:51 pm, Duane Hookom
wrote:
Josiah,
Did you check out my suggested solution from Tek-Tips? It doesn't require
any code.

--
Duane Hookom
Microsoft Access MVP

"Josiah" wrote:
On Aug 7, 2:11 pm, "Roger Carlson"
wrote:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CrossTabReport.mdb" which illustrates one way to do this, if
I understand your problem correctly. You can find the sample hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=362

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Josiah" wrote in message

....

Elements and Structu I have a Crosstab query which selects data
from a set of tables that hold all of the financial information for a
particular program within a company. This query is arranged such that
there are 5 rowwise fields which list an empoyee's name, department,
charge code, etc. Then, the columnwise field is based on the labor
charge date charged hours are associated with. The way the columnwise
field works is it operates on two parameters that are dates,which are
gathered from 2 combo boxes in a form. These 2 parameters are used to
determine the date range to pull labor charging information from. For
example, if combobox1 = 01/01/2007 and combobox2 = 02/01/2007, then
there will be a column heading for every date in between those 2 dates
for which there was labor charged. The smallest interval is 1 week,
so querying on a full month would never return more than 5 values
(because there cannot be more than 5 weeks in a month). The value
field, then, is the actual hours charged by a certain person on a
certain date under a certain charge number.

Goal: What I want to do is create a report based on this query. I
only need to display 6-7 weeks of data, so I will not need to include
more than 7 date fields in the report. If a date range of greater
than 7 weeks is selected, it is acceptable for me to just drop all
data falling after the 7 week period.

Problem: every textbox will have to be bound dynamically, and the date
field labels will have to be set dynamically at runtime in order to
get the desired report. I would like to be able to group information
on the first 3 rowwise fields of the crosstab query.

Where I am: since it is acceptable for me to just drop all data
falling after the 7 week period, in my code I only iterate on the
first 11 fields of my crosstab query. That way, any information
falling later than the acceptable timeframe is dropped without needing
to crash the system. I have all of the labels successfully displaying
the correct field names in the form. However, when it comes to actual
data in the textboxes, it seems the binding is not being done right,
because I get the message #Name? in each textbox. The way I tried to
accomplish binding was to go into the VBA code of the report, store a
QueryDef of my crosstab query, providing values for the parameters
based upon my form with the two date combo boxes. I then opened a
recordset of that query and stored it in a variable, lets call it,
rcdSet. So, to update the labels, I named the labels L00 - L11 and
then cycled through the fields in rcdSet, setting the caption of each
label to rcdSet.Fields(IterationNumber).Name. This works fine. But
when I tried the same thing witht he text boxes, naming the textboxes
M00-M11 and ctrl(M[IterationNumber]).ControlSource = rcdSet.Fields
(IterationNumber).Name All that turns up in my text boxes is #Name?..

Can someone help me out?

If you need sample code, let me know and I will cook something up.- Hide quoted text -

- Show quoted text -

I think right now I'm having problems because the columnwise datefield
in my crosstab is being restricted by a where clause 'where
charge_date between [forms!myform!cboBox1] and [forms!myform!
cboBox2]'. I am not sure how access works, but your example code runs
based on the assumption that the computer knows the crosstab query's
fields at runtime. For some reason, in my code, when i define my
QueryDef: myQryDef = myDB.QueryDefs("qryMyCrosstab"), If I call a
watch on myQryDef and look at the Fields, the Field Count is 0, even
if I have the query open in the database. If i call a recordset,
though: myRcrdSet = myQryDef.OpenRecordset(), I can get the fields.
So now, I have built my sql string "SELECT {qryMyCrosstab.fields as
Field0-11} from qryMyCrosstab", but I guess since, to Access,
qryMyCrosstab technically has 0 fields, I get an error message when I
try to run the query that basically says it was expecting a SELECT,
DELETE, PROCEDURE, etc. statement (even though I have a Select
statement). I think it is because the query cant access the fields in
my crosstab. is there any way around this? Am I right in my
conjectures, even?- Hide quoted text -

- Show quoted text -

I need to be able to display the actual date on the report, I cant do
the relative headings. Sorry, I would like taht solution, too, but it
doesnt meet the business needs here.- Hide quoted text -

- Show quoted text -

The link you sent me replaces the dates with Mth0-MthN. I can't have
that, I need to be able to display the date that's comming out of the
database because its all based on the financial labor charge date.
Those viewing the reports need the 'real date', according to
management.


A progress note, I have determined that in using a QueryDef to access
my crosstab query in VBA, the reason I cant see the fields is for sure
linked to my use of the 'WHERE Labor_Charge_date BETWEEN [forms!myForm!
cboFromDate.Value] AND [forms!myForm!cboToDate.Value]' phrase. I
recently created a copy of the database where I just always query on
the past 6 weeks of financial data and now in the VBA, when I create a
QueryDef of my crosstab, I can see all my fields when i 'watch' my
QueryDef.