Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Welcome to Woody's Lounge!

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

  3. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    Which table shows the field with no decinal places?

    What is the format of these fields in both tables?

  7. #7
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  9. #9
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Set the Format property of the field to Fixed.

  11. #11
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  13. #13
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  15. #15
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Ok. Thanks for clarifying that. Appreciate your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •