Conditional formatting in a Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a Crosstab Query I would like to have a fore colour for Sums up to 100 and
another fore color for Sums greater than 100 Thanks in advance
Mansoor
 
Mansoor said:
In a Crosstab Query I would like to have a fore colour for Sums up to 100 and
another fore color for Sums greater than 100 Thanks in advance
Click to expand...


Use a form (datasheet or continuous view) to display the
results of the query. Then you can use Conditional
Formatting to specify the color of a control.
 
It works with a tabular form.Thank You.
But now I have another problem.Since this form is generated by a crosstab
query and the fields are filtered months (>Date()),next month I will have the
field of January #Name? and the new months that I have added will not
appear.Is there a way to solve the problem? Thank You in advance
 
Right! Figured this would be the next question ;-)

This situation can not go on indefinitely because you will
eventually run out of space on the form. So, you have to
decide on a maximum number of months to display on the form.

If, for example, you will always want one years worth of
data, you can prespecify the months by using the crosstab
query's Column Headings property.

If that won't meet your needs, then you should use an
expression for the field that's used for the column header
(Pivot) that makes the row heading a relative month number:
"M" & DateDiff("m", yourdatefield, Date())
which allows you to bind the form controls to the field
names M0, M1, ... If the form then uses a text box for the
heading labels, you can display the month names with sort of
the reversee expression
=Format(DateAdd("m", 0, Date()), "mmmm")
=Format(DateAdd("m", -1, Date()), "mmmm")
. . .

If none of that comes close to what you need, post back with
more detail about what you do need.
 
What I need, is always one year worth of data,and You wrote "you can
prespecify the months by using the crosstab query's Column Heading
property".Sincerely I do not know what to write on the Column Heading
property.
I will be gratefull if You can guide me.
Thank you

Marshall Barton said:
Right! Figured this would be the next question ;-)

This situation can not go on indefinitely because you will
eventually run out of space on the form. So, you have to
decide on a maximum number of months to display on the form.

If, for example, you will always want one years worth of
data, you can prespecify the months by using the crosstab
query's Column Headings property.

If that won't meet your needs, then you should use an
expression for the field that's used for the column header
(Pivot) that makes the row heading a relative month number:
"M" & DateDiff("m", yourdatefield, Date())
which allows you to bind the form controls to the field
names M0, M1, ... If the form then uses a text box for the
heading labels, you can display the month names with sort of
the reversee expression
=Format(DateAdd("m", 0, Date()), "mmmm")
=Format(DateAdd("m", -1, Date()), "mmmm")
. . .

If none of that comes close to what you need, post back with
more detail about what you do need.
--
Marsh
MVP [MS Access]


It works with a tabular form.Thank You.
But now I have another problem.Since this form is generated by a crosstab
query and the fields are filtered months (>Date()),next month I will have the
field of January #Name? and the new months that I have added will not
appear.Is there a way to solve the problem? Thank You in advance
Click to expand...
Click to expand...
 
Set it to the list of values you want the query to use. If
you're using the names of months, then you could set it to:
January,February, . . .
 
I think I was not clear in my questions.
My crosstab query gives me this results in february:
01/02/05 01/03/05 .........01/01/06
x y ......... z

In March I wiil have:(Criteria: (>Date())
01/03/05 .................. 01/01/06 01/02/06
Y z w

Instead in my tabular form (With the conditional formatting) I get in
february:(Criteria: (>Date())
01/02/05 01/03/05 .........01/01/06
x y ......... z
and in March I will have:
01/02/05 01/03/05 .........01/01/06
#Name? y ......... z
Instead of:
01/03/05 .................. 01/01/06 01/02/06
Y z w
The Control Source for march is: 01/03/05
I am sorry in not being clear:
Thanks
Mansoor


Marshall Barton said:
Set it to the list of values you want the query to use. If
you're using the names of months, then you could set it to:
January,February, . . .
--
Marsh
MVP [MS Access]

What I need, is always one year worth of data,and You wrote "you can
prespecify the months by using the crosstab query's Column Heading
property".Sincerely I do not know what to write on the Column Heading
property.
I will be gratefull if You can guide me.
Thank you
Click to expand...
Click to expand...
 
You were clear enought before. I think I was the one that
didn't explain clearly.

If you do what I recommended, then your crosstab would have
these Febuary results:
M11 M10 . . . M0
x y z

and these March Results:
M11 . . . M1 . . . M0
y z w

Your form would use text boxes bound to the Mx fields and
use heading text boxes with expressions like I posted to
display that 01/02/05 etc.

Give it a try and try to work out any kinks between what I
posted and your specific situation. If there's a detail
that's still giving you trouble, post back with the query
and what happened.
 
To Marshal Barton
I appreciate You having a lot of patience in helping me, but it was
worthy,for me, finally after ten days I got it and it works perfectly. I
changed also the heading labels in text box as you wrote me at the beginning
and everything is perfect.
I must admitt that you introduced me in a new dimension in understanding the
crosstab and I thank you for that.
Mansoor Cohen

Marshall Barton said:
You were clear enought before. I think I was the one that
didn't explain clearly.

If you do what I recommended, then your crosstab would have
these Febuary results:
M11 M10 . . . M0
x y z

and these March Results:
M11 . . . M1 . . . M0
y z w

Your form would use text boxes bound to the Mx fields and
use heading text boxes with expressions like I posted to
display that 01/02/05 etc.

Give it a try and try to work out any kinks between what I
posted and your specific situation. If there's a detail
that's still giving you trouble, post back with the query
and what happened.
--
Marsh
MVP [MS Access]


I think I was not clear in my questions.
My crosstab query gives me this results in february:
01/02/05 01/03/05 .........01/01/06
x y ......... z

In March I wiil have:(Criteria: (>Date())
01/03/05 .................. 01/01/06 01/02/06
Y z w

Instead in my tabular form (With the conditional formatting) I get in
february:(Criteria: (>Date())
01/02/05 01/03/05 .........01/01/06
x y ......... z
and in March I will have:
01/02/05 01/03/05 .........01/01/06
#Name? y ......... z
Instead of:
01/03/05 .................. 01/01/06 01/02/06
Y z w
The Control Source for march is: 01/03/05
I am sorry in not being clear:
Thanks
Mansoor
Click to expand...
Click to expand...
 
Back
Top