Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    1 decimal only (Access2000-03)

    Hi!
    I have a text field that needs to have just 1 decimal point.
    What function should I be using to search for the dot and leave 1 character after it?

    THANKS

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

    Re: 1 decimal only (Access2000-03)

    You should use a number field, not a text field, and set its Format property to 0.0 or to #,##0.0 (the latter format will display thousands separators if appropriate).

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    It is prety hard to have a numeric field if you are making tables 3 times before Final table is made after which precedure you have to delete field by field and result you have is a text field!
    It is mush easier here to use the function to format the field on Report level, don't you think?
    Thanks

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

    Re: 1 decimal only (Access2000-03)

    Sorry, I don't understand what you're trying to say.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    I am trying to find a formula that will take my result and leave everything before the dot and 1 decimal after.
    Thanks

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

    Re: 1 decimal only (Access2000-03)

    Try
    <code>
    IIf(InStr([FieldName],"."),Left([FieldName],InStr([FieldName],".")+1),[FieldName])
    </code>
    where FieldName is the name of the field.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I still think you should use a number field.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    It is not that I disagree...I do not see how can I make this field Numeric if it is result of division of 2 fields?
    It is made by Make Table query. It looks like this:AVG: Sum([CountOfPERSON_ID]/[CountOfRecID])

    Any suggestions will be greatly appreciated! Thanks<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: 1 decimal only (Access2000-03)

    I'd expect the result of a sum and a division to be numeric by default!

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    Somehow it makes text field! Go figure...

    BTW that formula makes fields #error# - any suggestions there? Thanks

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

    Re: 1 decimal only (Access2000-03)

    I don't understand what's happening. I'd have to see a stripped down copy of the database.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    All right, I found the 'thing'....
    I am using tables that are views. From Oracle linked into Access.
    One of those views has fields I am calculating - text datatype.
    What can I do to work around this issue?

    Thanks and have a good weekend!

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

    Re: 1 decimal only (Access2000-03)

    What happens if you use

    AVG: Sum(Val([CountOfPERSON_ID])/Val([CountOfRecID]))

    in your make-table query?

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    OK, I see now.
    One of my Make table Queries has AVG field as AVG:""
    And second query Appends result into this AVG:""
    AVG is text. Is there way to change the query to set AVG:"" as Numeric?
    Thanks

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

    Re: 1 decimal only (Access2000-03)

    Try

    AVG: Null

    or else

    AVG: 0

  15. #15
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 decimal only (Access2000-03)

    0 worked! Null gave Binary...
    Thanks!!!!!!!!!!!

Posting Permissions

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