Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimal Place Removal (Access 2002)

    Hi

    I have a union query reporting from a linked informix back end. The union query is used to export data into an excel spreadsheet. In one of fields - debtor code (a string field) records may or may not contain decimal place. For those records that do contain a decimal place I need to remove it. So 123456.01 becomes 12345601. It should be noted the number of characters in each record can vary.

    It is been awhile since I have had to do this sort of thing, so my memory is a bit hazy on how to go about it. Any suggestions appreciated.

    Regards

    WTH

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

    Re: Decimal Place Removal (Access 2002)

    You can use the Round function for this: Round([Fieldname],0) will round the value of Fieldname to the nearest integer. If you'd rather truncate the number (i.e. 123.89 becomes 123 instead of 124), you can use Int([Fieldname]).

    You'll have to apply the function to the appropriate field in each of the contributing SELECT statements, or create a new query based on the union query and apply the function there.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Decimal Place Removal (Access 2002)

    I think you need to use the Replace function to remove the decimal point in the string, and replace it with nothing.

    debtorcode2: Replace([debtorcode],".","")
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Place Removal (Access 2002)

    Hi Hans

    Thanks for the speedy reply. However, I don't think I have explained my problem sufficiently well based on your reply.

    The debtor code is a string field and any one record may either contain only numbers or a mixture of numbers and characters eg RE12234, or 23456.01. Where the record only contains numbers and it has a decimal place I only wish to remove the decimal place (or point) and not the numbers after it

    Eg

    Before After
    123456.01 12345601
    5689.02 568902
    RE456 RE456

    Regards
    WTH

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal Place Removal (Access 2002)

    Hi

    Thanks. The replace function has done the job. Should have remembered this.

    Regards
    WTH

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

    Re: Decimal Place Removal (Access 2002)

    Sorry, I misinterpreted your question. Fortunately, John provided the solution.

Posting Permissions

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