UPDATE QUERY - DECIMAL PLACES

[ANDY-N via AccessMonster.com]

I have a table that gets updated every week. However, each week, this table
is deleted by a MAKE TABLE query so the number format doesn't hold. So I
created an update query to fix this problem. I want the number format to have
2 decimal places. This works fine, however, there are negative numbers in the
table which get changed to positive numbers when this update query runs. I
can't figure out how to fix this problem. The construct for the update query
is followed:

UPDATE [MASTER TABLE] SET YTD = FORMAT([YTD],"##,###.00;0; -##,###.00"), JAN
= FORMAT([JAN],"##,###.00;0; -##,###.00"), FEB = FORMAT([FEB],"##,###.00;0; -
##,###.00"), MAR = FORMAT([MAR],"##,###.00;0; -##,###.00");

Please help. Thanks!

 

[Jerry Whittle]

Well the Format function returns a string so it looks like a number but
really isn't.

Instead of creating a new table with a Make Table query, why not just delete
all the records in the existing table then import / append the new data into
it?

 

[J_Goddard via AccessMonster.com]

Hi -

I suggest you need not bother with formatting the data in the table in the
first place, since the data in a table is usually viewed in a form, report or
select query, and not just by opening the table.

Another consideration is that your update query changes all the data from
numeric to text, making any calculations very difficult.

... there are negative numbers in the
table which get changed to positive numbers when this update query runs. I
can't figure out how to fix this problem.

I cannot see how this can be happening - your FORMAT statements look OK .
But, as I said above, don't bother formatting the table data - use forms and
queries to display the data the way you want.

John

I have a table that gets updated every week. However, each week, this table
is deleted by a MAKE TABLE query so the number format doesn't hold. So I
created an update query to fix this problem. I want the number format to have
2 decimal places. This works fine, however, there are negative numbers in the
table which get changed to positive numbers when this update query runs. I
can't figure out how to fix this problem. The construct for the update query
is followed:

UPDATE [MASTER TABLE] SET YTD = FORMAT([YTD],"##,###.00;0; -##,###.00"), JAN
= FORMAT([JAN],"##,###.00;0; -##,###.00"), FEB = FORMAT([FEB],"##,###.00;0; -
##,###.00"), MAR = FORMAT([MAR],"##,###.00;0; -##,###.00");

Please help. Thanks!

 

[ANDY-N via AccessMonster.com]

Thanks for the replies guys. The reason for remaking the table each time is
that I want to automate this process. This table contains all the information
I want to pull from using queries, these are displayed in forms. I format the
queries so it display the right number format, however, some of the fields
hold their decimal places and some do not (this is due to the SUM function).
The update query worked well and every fields hold the right decimal places,
except the negative turn into positive.

Hi -

I suggest you need not bother with formatting the data in the table in the
first place, since the data in a table is usually viewed in a form, report or
select query, and not just by opening the table.

Another consideration is that your update query changes all the data from
numeric to text, making any calculations very difficult.

... there are negative numbers in the
table which get changed to positive numbers when this update query runs. I
can't figure out how to fix this problem.

I cannot see how this can be happening - your FORMAT statements look OK .
But, as I said above, don't bother formatting the table data - use forms and
queries to display the data the way you want.

John

I have a table that gets updated every week. However, each week, this table
is deleted by a MAKE TABLE query so the number format doesn't hold. So I

[quoted text clipped - 9 lines]

Please help. Thanks!

 

[J_Goddard via AccessMonster.com]

Hi -

You are "losing" your negative values because your format functions are
incorrect. When you use the format function with multiple formats for
different values, the *second* option applies to negative values, not the
third.

In your case, you don't need three formats anyway: FORMAT([YTD],"##,###.00")
will include the minus sign for negative values.

John

Thanks for the replies guys. The reason for remaking the table each time is
that I want to automate this process. This table contains all the information
I want to pull from using queries, these are displayed in forms. I format the
queries so it display the right number format, however, some of the fields
hold their decimal places and some do not (this is due to the SUM function).
The update query worked well and every fields hold the right decimal places,
except the negative turn into positive.

Hi -

[quoted text clipped - 20 lines]