How do I get numbers to show with decimal places? (2002)

Topic

New Reply

I’m a newbie to this forum so please be gentle. I’ve created a make table query with most of the fields pulled from 2 tables. I’ve created one new field as follows…
LastPrice$: 0. Later on an update query then populates this field with prices from another table (called QEC list with latest price). The data type for the field in table QEC list with latest price is Number, Field Size Double and Decimal Places Auto. When this table is viewed the prices show correctly with a decimal point in the format 00.00. My problem is that when the update query is run and populates the LastPrice$ field I created, the prices show as whole numbers and no decimal places. I’m not sure what I’m doing wrong. How can I get Access to retain the number formatting from table QEC list with latest price?

I hope this makes sense. If you need any further explanation please let me know. I really appreciate your help on this.

Reply | Quote

Replies

Welcome to Woody’s Lounge!

Set the Format property of the field to Currency or to a custom format such as £#,##0.00

Reply | Quote

Thanks for such a quick reply. Which table field show I be amending? The MTQ or the table the prices originates in?

Reply | Quote

Sorry, I didn’t think my reply through. With a make-table query, you have no control over the format of the fields in the target table.

I’d do it like this instead:
– Create a table with the structure you need – configure the fields the way you want them.
– Use an append query to populate the table.
– If you want new records to replace the old one, run two queries in succession:
1. A delete query to remove all existing records.
2. An append query to insert the new records.

Reply | Quote

I don’t think an append query can help me. I have data from multiple tables being updated into a single table that has been created by my MTQ. I will keep ‘playing’ and see what I come up with. Thanks anyway.

Reply | Quote

Which table shows the field with no decinal places?

What is the format of these fields in both tables?

Reply | Quote

Tbl 1 contains stock balances for hundreds of parts.
Tbl 2 contains a list of part numbers that I’m interested in for a certain exercise.
Tbl 3 contains prices for each of the parts in tbl 2. The table containing two fields – PartNumber and Price. The data type of the ‘Price’ field is Number, Field Size Double, Format Fixed, Decimal Places Auto. The prices show correctly in this table (e.g. 125.85). I don’t need to see the currency symbol.
My make table query links tbl 1 part number to tbl 2 part number (join property 1). The fields I want to see in the table it produces come from tbl 1 as well as some new fields I create that will be populated later by several update queries. One of the new fields is ‘PriceEach’. I input this as ‘PriceEach: 0’. So initially when the MTQ is created, this field shows zeros and is identified as a data type of Number.

When I later run an update query to take the price from tbl 3 and populate them in my MTQ (against the correct part numbers), the formating is all wrong. The prices are shown without any decimal point. Where am I going wrong?

Reply | Quote

Access doesn’t know that you want to fill the field PriceEach with Double precision numbers later on. The expression

PriceEach: 0

will create a Long Integer field that can only hold whole numbers. To force Access to create a Double precision number field, use

PriceEach: CDbl(0)

The CDbl function converts the integer 0 to a double precision number.

Reply | Quote

Ok. I seem to be a little closer. The resulting MTQ table now shows the prices but if its a whole number like ‘128.00’, this appears on my table like ‘128’. If the price is ‘6.50’ it shows on my table as ‘6.5’.How can I make sure the decimal point is always shown and with two digits after it? Many thanks.

Reply | Quote

Set the Format property of the field to Fixed.

Reply | Quote

The MTQ Format property of the field is already set to fixed.

Reply | Quote

You must set the Format property of the PriceEach field to Fixed in the target table after running the make-table query.

Reply | Quote

Thanks, that worked. Is there any other way of making this formatting happen during the query stage? I was hoping to have put my MTQ along with 12 other queries on a single button with a macro that would run the whole lot. Any ‘tweaking’ with a table after its creation would be a problem.

Reply | Quote

You cannot specify the format of a target field in a make-table query. You have to set it after running the query, and you have to do it each time since the make-table query deletes the target table before creating it again.
That’s why I suggested using a delete query plus an append query instead of a make-table query. That way, the structure of the target table remains intact.

Reply | Quote

Ok. Thanks for clarifying that. Appreciate your time.

Reply | Quote