Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Types (2000)

    Hi I have the following conditional parameter in a query grid:

    IIf([detail_contract].[contract_term]>0,[detail_contract].[contract_usage]/[detail_contract].[contract_term])

    both contract_term and contract_usage are Fixed numeric fields with decimal place set to 0, but the result of the query returns 1.6666 etc and the field seems to think its a data field. I need it to be numeric also - can anyone help?

    Thanks VM.

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

    Re: Field Types (2000)

    If you divide 10 by 6 (for example) you get 1.66666. What answer do you want displayed in that case?
    Regards
    John



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

    Re: Field Types (2000)

    The division operator / results in a number that may have decimals, even if the numerator and denominator are whole numbers, for example 5 / 3 = 1.66666...
    If you want the displayed result rounded to the nearest whole number, set the Format property of the calculated column to Fixed and Decimal Places to 0.
    If you need the rounded value for further calculations, you can use

    Int(IIf([detail_contract].[contract_term]>0,[detail_contract].[contract_usage]/[detail_contract].[contract_term])+0.5)

    I don't understand what you mean by "seems to think its a data field. I need it to be numeric".

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Field Types (2000)

    >>...both contract_term and contract_usage are Fixed numeric fields with decimal place set to 0...<<

    You need to understand the difference between a field type and formatting. What you described above are formatting properties, they don't give any clue as to what the field type is. That is, they could be integers, floating point, or even currency! Dividing 2 numbers generally results in a double floating point result. You can then format that result like you want it using the Format() function.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Types (2000)

    Hi folks. I had already been trying to set the field property as decribed, but it would only let me format as per dates., not fixed/decimal I'm afraid some of the rest of what you are saying is too advanced for me to understand, however, I'm working around this prob at the moment. thanks for trying to help.

    M

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

    Re: Field Types (2000)

    You cannot always select all possible formats from the dropdown list, but you can enter the word Fixed manually in the Format property.

Posting Permissions

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