Help a VBA/Macro beginner.

[Guest]

I do not have any background in creating VBA code and very basic knowledge of
macros in Access so please bear with me.

First let me give a brief description of my problem.

I am pulling data from my business software database, I have the tables set
up as a linked table. Then writing a make table query to join a bunch of
tables together to create a new table which I will use in Excel to do
extensive analysis and charting of the business data. The problem I am
having is when I try to create/refresh my pivot table in Excel, after I have
refreshed/re-run my query, I get an error message stating “Invalid character
value for cast specification on column number 34 (sales Amt) in Excel.

I was able to find out why this is coming up. It has to do with the scale
that is set on the design properties for the new table that is created from
the query. When it creates the table it has a scale of 2 for the Sales Amt
field. The Sales Amt field is taking the Price * Shipped fields, the price
has a scale of 2 and the shipped has a scale of 0. I am also calculating a
Cost Amt (Cost*shipped), the cost has a scale of 3 and the shipped has a
scale of 0 and the Cost Amt field has a scale of 3. What is confusing to me
is the cost amt works fine but the sales amt does not, unless I go into the
design properties for the new table and manually change it to a scale of 3.

I am wondering if any of you experts know of a way either through a macro or
some VBA code to automate changing this field to a scale of 3 in the design
properties for the table. Or if there is some thing I can do in the query to
change the scale that gets created when it makes the new table for the sales
amt field. I have tried changing the properties on the price and sales amt
fields in the query design to no avail.

If you need more clarification or would like an example of the table let me
know.

Thanks in advance.

 

[tina]

suggest you re-use the same table, rather than making a new table each time.
run a Delete query to delete the data from the table, then run an Append
query (rather than a Make-Table query) to dump the new data into the
existing table.

hth

 

[Guest]

Tina,
Thank you for you suggestion, this works great.

 

[tina]

you're welcome

Tina,
Thank you for you suggestion, this works great.