Crosstab and Form questions

[Sherman H.]

I have a few questions for crosstab and popup form questions:

1. I created a crosstab as follows:

IT Financial Operation
John 21 22 22
Mary 15 17 16
Joe 28 29 31

For some other instances, the Operation column was not created because the
source table/query does not have an entry with value "Operation". However,
I need the column in a report that uses the crosstab query. How can I set
the Operation column in the report even when the source table doesn't have
"Operation" entry?
2. I used a text filed "Start Date" from a form as a query criteria. The
query is then used to create a crosstab query. Somehow, an error message
indicated that the text field cannot be recognized by the Jet Engine. I
then changed the query type to "Make Table". It worked. Is this the right
to do it?
3. A popup form is created to preview a report. How to bring the report
preview to the front after the "Print" button is clicked?
4. In the form mentioned at 3, I have the "Start Date" field. The start
date should be a Sunday date. I would like to have edit check to verify if
the entered date is a Sunday. I know I can use Format to convert the date
to weekday and check it. Where and how the edit routine should be written?

Sorry, it's been two years I left the Access programming. Any help is
appreciated.

[MacDermott]

Ah, I remember having all these questions myself when I was first learning
about crosstab queries!

1. In the property sheet of the crosstab query, you can set ColumnHeaders
like this:
"IT";"Financial";"Operation"
Then all of these columns (and only these columns) will always appear, even
if they have no data.

2. For a crosstab query based on a parameter query, declare the parameters
explicitly in both queries:
Open the Parameters dialog from Query - Parameters.
In the left-hand column, enter the parameter as it appears in the
original query, e.g.
Forms!Form2![Start Date]
In the right-hand column, select the data type.
After you've done this, the crosstab query should run without any need
for the overhead of a make-table.

3. Here's one way:
In the report's Open event, set the form's Visible property to
False.
In the report's Close event, be sure to either close the form, or
set its Visible property back to True.

4. Where you do the checking is up to you, but I'd suggest in the
BeforeUpdate event of the textfield.
That way if the date is not a Sunday, you can set
Cancel=True
And focus will not leave the field until the criteria are met.
(Hint: You may want to include a special case to let you leave the
field if it is blank.)

HTH
- Turtle

"Sherman H." <sh***@charter.net> wrote in message
news:10*************@corp.supernews.com...

I have a few questions for crosstab and popup form questions:

1. I created a crosstab as follows:

IT Financial Operation
John 21 22 22
Mary 15 17 16
Joe 28 29 31

For some other instances, the Operation column was not created because the
source table/query does not have an entry with value "Operation". However, I need the column in a report that uses the crosstab query. How can I set
the Operation column in the report even when the source table doesn't have
"Operation" entry?
2. I used a text filed "Start Date" from a form as a query criteria. The
query is then used to create a crosstab query. Somehow, an error message
indicated that the text field cannot be recognized by the Jet Engine. I
then changed the query type to "Make Table". It worked. Is this the right to do it?
3. A popup form is created to preview a report. How to bring the report
preview to the front after the "Print" button is clicked?
4. In the form mentioned at 3, I have the "Start Date" field. The start
date should be a Sunday date. I would like to have edit check to verify if the entered date is a Sunday. I know I can use Format to convert the date
to weekday and check it. Where and how the edit routine should be written?
Sorry, it's been two years I left the Access programming. Any help is
appreciated.

[G.J. v.d. Kamp]

"Sherman H." <sh***@charter.net> wrote in message news:<10*************@corp.supernews.com>...

I have a few questions for crosstab and popup form questions:

1. I created a crosstab as follows:

IT Financial Operation
John 21 22 22
Mary 15 17 16
Joe 28 29 31

For some other instances, the Operation column was not created because the
source table/query does not have an entry with value "Operation". However,
I need the column in a report that uses the crosstab query. How can I set
the Operation column in the report even when the source table doesn't have
"Operation" entry?
That is a problem when you use a crosstab with the TRANSFORM .. PIVOT
syntax in Access. The best solution would be to learn about crosstabs
the way they're used in SQL server etc, you can find an * excellent *
article about that here:

http://mysql.mirror.nedlinux.nl/tech...ticles/wizard/

(Make sure you use IIF instead of IF (mysql)). This might take a while
to get your head around but once you understand you will have grown...

I made a module in VB 6.0 once that could translate the TRANSFORM ..
PIVOT syntax into something any DB server could understand, if you
have VB you can find it here:

http://www.freevbcode.com/ShowCode.asp?ID=6076
That my two cents, can't help you with the other questions.

Good luck,

GJ

2. I used a text filed "Start Date" from a form as a query criteria. The
query is then used to create a crosstab query. Somehow, an error message
indicated that the text field cannot be recognized by the Jet Engine. I
then changed the query type to "Make Table". It worked. Is this the right
to do it?

3. A popup form is created to preview a report. How to bring the report
preview to the front after the "Print" button is clicked?
4. In the form mentioned at 3, I have the "Start Date" field. The start
date should be a Sunday date. I would like to have edit check to verify if
the entered date is a Sunday. I know I can use Format to convert the date
to weekday and check it. Where and how the edit routine should be written?

Sorry, it's been two years I left the Access programming. Any help is
appreciated.