How do I add fields to an existing table using DAO VBA?

[Rich NG]

I have an Access 2000 application that has DAO VBA code. Part of this
application generates a bar chart showing counts of parts using color codes.
These color codes can have one of 3 values: R, G, B (Red, Green, Blue,
respectively).

If I do a Crosstab query and there are no values "R" in the table, I will
not get a field name "R" in the resultant crosstab. This causes the charting
feature to generate an error.

It strikes me as the only way to overcome this is to do the crosstab query
using a MakeTable option to create a temporary table called COLOR_COUNT.
Then I would go to the original table and, using DCOUNT, check to see if
there are any R, G, or B values. If the value does not exist, (e.g., if the
value "R" does not exist), I want to add a field "R" to the COLOR_COUNT table.

Unfortunately, I haven't been able to figure out how to do this.

Any and all help will be greatly appreciated!

Rich

 

[Ron Weiner]

You can add an IN() clause just after the PIVOT clause in the Sql. With out
seeing gyour Sql I am just guessing that adding IN('R','G','B') just after
PIVOT ColumnName.

Ron W