I have a datasheet that shows sold quantities for each month. I want to 
highlight column that shows quantities of current month. So for this month, 
that will be June 2007. Next month, that will be July 2007. As far as I know, 
conditional formatting changes cells format based on the value of the cell. 
Is there a way to format the column based on the column header?
Thanks in advance.

You've got a basic mistake here; in Access records are set up as Rows, not
Columns! A given month's record will be a Row going across. Your various
Fields will be in the Columns. Then Conditional Format the Quantities field
for the current month using the Expression Is and check the Month/Year of the
record to see if it's equal to the current Month/Year.

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1

Thanks for your reply, but this is how i setup my data (simplified):
Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07
ABC     Item1     0                    15            20       17     10
ABC     Item2     10                   5             24       0       0
BDC     Item3     20                    15           120       17     10
BCE     Item1     0                      15            20       17     10
BCE     Item3     0                      15            20       17     10

Thanks.

"missinglinq via AccessMonster.com" wrote:

> You've got a basic mistake here; in Access records are set up as Rows, not
> Columns! A given month's record will be a Row going across. Your various
> Fields will be in the Columns. Then Conditional Format the Quantities field
> for the current month using the Expression Is and check the Month/Year of the
> record to see if it's equal to the current Month/Year.
> 
> -- 
> There's ALWAYS more than one way to skin a cat!
> 
> Answers/posts based on Access 2000
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1
> 
> 

On Fri, 8 Jun 2007 08:58:02 -0700, lwidjaya
<lwidjaya@discussions.microsoft.com> wrote:

>Thanks for your reply, but this is how i setup my data (simplified):
>Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07
>ABC     Item1     0                    15            20       17     10
>ABC     Item2     10                   5             24       0       0
>BDC     Item3     20                    15           120       17     10
>BCE     Item1     0                      15            20       17     10
>BCE     Item3     0                      15            20       17     10

Then you have your data set up INCORRECTLY.

You're "committing spreadsheet upon a database". A database is NOT a
spreadsheet and doesn't work like one. "Fields are expensive, records are
cheap" - a much better design would be

Cust   Item  SaleDate  Actual  Forecast
ABC   Item1 #2/1/07#  0        0
ABC   Item1  #3/1/07# 10      25
ABC   Item1  #4/1/07# 13      25
....
BCE   Item3  #6/1/07#  20     17

You can create a Crosstab Query to spread the records out into columns *for
display purposes only*.

             John W. Vinson [MVP]

OK, here's the thing:
the data is originally set up like this:
Cust   Item  SaleDate   Qty
 ABC   Item1 #2/1/07#  0       
 ABC   Item1  #2/17/07# 10     
 ABC   Item1  #4/1/07# 13     
 ....
 BCE   Item3  #6/1/07#  20    

Now the users want to see the data in this format:
Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07

SaleDate and Qty are actually the ActualDate and ActualQty, so I need to add 
the Forecast columns. Users want to be able to enter forecasting data in 
those Forecast columns. That's why I create a table based on the crosstab 
query of the original table and add the Forecast columns.

If I did this all wrong, is there another way to do it?
Thanks in advance.

Lisa
"John W. Vinson" wrote:

> On Fri, 8 Jun 2007 08:58:02 -0700, lwidjaya
> <lwidjaya@discussions.microsoft.com> wrote:
> 
> >Thanks for your reply, but this is how i setup my data (simplified):
> >Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07
> >ABC     Item1     0                    15            20       17     10
> >ABC     Item2     10                   5             24       0       0
> >BDC     Item3     20                    15           120       17     10
> >BCE     Item1     0                      15            20       17     10
> >BCE     Item3     0                      15            20       17     10
> 
> Then you have your data set up INCORRECTLY.
> 
> You're "committing spreadsheet upon a database". A database is NOT a
> spreadsheet and doesn't work like one. "Fields are expensive, records are
> cheap" - a much better design would be
> 
> Cust   Item  SaleDate  Actual  Forecast
> ABC   Item1 #2/1/07#  0        0
> ABC   Item1  #3/1/07# 10      25
> ABC   Item1  #4/1/07# 13      25
> ....
> BCE   Item3  #6/1/07#  20     17
> 
> You can create a Crosstab Query to spread the records out into columns *for
> display purposes only*.
> 
>              John W. Vinson [MVP]
> 

You're "committing spreadsheet upon a database"

I love that, John! You know I'll use it! Of course I'll cite you! It's
amazing to me how many times a day you see this committed here and on other
forums!

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1

On Tue, 12 Jun 2007 09:47:01 -0700, lwidjaya
<lwidjaya@discussions.microsoft.com> wrote:

>OK, here's the thing:
>the data is originally set up like this:
>Cust   Item  SaleDate   Qty
> ABC   Item1 #2/1/07#  0       
> ABC   Item1  #2/17/07# 10     
> ABC   Item1  #4/1/07# 13     
> ....
> BCE   Item3  #6/1/07#  20    

That's the correct setup. You may need additional fields, or perhaps an
additional table, for forecast quantities.

>Now the users want to see the data in this format:
>Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07

Don't confuse data STORAGE with data PRESENTATION. 

>SaleDate and Qty are actually the ActualDate and ActualQty, so I need to add 
>the Forecast columns. Users want to be able to enter forecasting data in 
>those Forecast columns. That's why I create a table based on the crosstab 
>query of the original table and add the Forecast columns.

A Crosstab query can never be updateable (because the fields are summaries of
multiple records, in a general sense). You'll need to either create a
denormalized "scratchpad" table or use an unbound Form with the 24 textboxes
for the actual and forecast values, and provide code to move the data back
into the normalized table; or... gasp... train your users to think vertically
instead of horizontally.

             John W. Vinson [MVP]

Hi guys, thanks for all your inputs. 

"John W. Vinson" wrote:

> On Tue, 12 Jun 2007 09:47:01 -0700, lwidjaya
> <lwidjaya@discussions.microsoft.com> wrote:
> 
> >OK, here's the thing:
> >the data is originally set up like this:
> >Cust   Item  SaleDate   Qty
> > ABC   Item1 #2/1/07#  0       
> > ABC   Item1  #2/17/07# 10     
> > ABC   Item1  #4/1/07# 13     
> > ....
> > BCE   Item3  #6/1/07#  20    
> 
> That's the correct setup. You may need additional fields, or perhaps an
> additional table, for forecast quantities.
> 
> >Now the users want to see the data in this format:
> >Cust    Item      ActualFeb07  ... ActulJun07   ForecastJun 07 ForecastJul07
> 
> Don't confuse data STORAGE with data PRESENTATION. 
> 
> >SaleDate and Qty are actually the ActualDate and ActualQty, so I need to add 
> >the Forecast columns. Users want to be able to enter forecasting data in 
> >those Forecast columns. That's why I create a table based on the crosstab 
> >query of the original table and add the Forecast columns.
> 
> A Crosstab query can never be updateable (because the fields are summaries of
> multiple records, in a general sense). You'll need to either create a
> denormalized "scratchpad" table or use an unbound Form with the 24 textboxes
> for the actual and forecast values, and provide code to move the data back
> into the normalized table; or... gasp... train your users to think vertically
> instead of horizontally.
> 
>              John W. Vinson [MVP]
>