Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Unexpected results

Discussion in 'Information Technology' started by hughess7, Jul 28, 2006.

  1. hughess7

    hughess7
    Expand Collapse
    Guest

    Hi all

    I am analysing some customers data and it has a totals value in a header
    file and split by labour, parts etc in other tables. I have performed
    calculations and done a comparison query to make sure the totals compute. I
    have compared the totals field with my calculated totals in the query by
    using <> [Totals] in the criteria but it displays totals that are the same?

    I thought maybe it was due to decimal places and rounding up but the data
    seems to be 2 decimal places so I don't understand what is happening? Anyone
    any ideas?

    Thanks
    Sue
    --
    Thanks in advance for any help.
    Sue
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    hughess7 wrote:
    ...
    >
    > the
    > data seems to be 2 decimal places
    >
    > Thanks
    > Sue


    Access can display data in many different ways and just looking at the
    displayed data is not a good way of determining how Access is actually
    storing or using data. Check the table definition of the field(s) and tell
    us what field types they are.

    --
    Joseph Meehan

    Dia duit
     
  4. hughess7

    hughess7
    Expand Collapse
    Guest

    The stored value is Double, 2 decimals (also tried changing to fixed), the
    other comparitive value was in a calculated control in a query definition.

    Just for the purposes of this exercise I created another Totals field in the
    table with same field definition but still did not work.

    Very odd...

    --
    Thanks in advance for any help.
    Sue


    "Joseph Meehan" wrote:

    > hughess7 wrote:
    > ...
    > >
    > > the
    > > data seems to be 2 decimal places
    > >
    > > Thanks
    > > Sue

    >
    > Access can display data in many different ways and just looking at the
    > displayed data is not a good way of determining how Access is actually
    > storing or using data. Check the table definition of the field(s) and tell
    > us what field types they are.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  5. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    The two decimal points are what it shows - not actually what is stored. Also
    you mentioned a calculated control. Sometimes they do not return exactly what
    you would expect. Access, and many other programs, have a floating point math
    problem. Here's an infamous example:

    Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
    -0.01.

    Try formatting the calculated control in the query to show only two
    decimals. Something like:

    <> Format([Totals], "0.00")
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "hughess7" wrote:

    > The stored value is Double, 2 decimals (also tried changing to fixed), the
    > other comparitive value was in a calculated control in a query definition.
    >
    > Just for the purposes of this exercise I created another Totals field in the
    > table with same field definition but still did not work.
    >
    > Very odd...
    >
    > --
    > Thanks in advance for any help.
    > Sue
    >
    >
    > "Joseph Meehan" wrote:
    >
    > > hughess7 wrote:
    > > ...
    > > >
    > > > the
    > > > data seems to be 2 decimal places
    > > >
    > > > Thanks
    > > > Sue

    > >
    > > Access can display data in many different ways and just looking at the
    > > displayed data is not a good way of determining how Access is actually
    > > storing or using data. Check the table definition of the field(s) and tell
    > > us what field types they are.
    > >
    > > --
    > > Joseph Meehan
    > >
    > > Dia duit
    > >
    > >
    > >
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Jerry Whittle wrote:
    > > The stored value is Double, 2 decimals (also tried changing to fixed), the
    > > other comparitive value was in a calculated control in a query definition.

    >
    > Sometimes they do not return exactly what
    > you would expect.


    Indeed...

    > Access, and many other programs, have a floating point math
    > problem. Here's an infamous example:
    >
    > Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
    > -0.01.


    Be careful here.

    The OP alluded to a calculation in a query, which may give different
    results to VBA.

    For example, in VBA:

    Debug.Print IIf(3.1 - 3.11 = -0.01, "Y", "N")

    returns 'N', whereas the equivalent in SQL:

    SELECT IIf(3.1 - 3.11 = -0.01, "Y", "N") FROM AnyTable;

    returns 'Y'.

    The reason is revealed when you do

    TYPENAME(3.1 - 3.11 = -0.01)

    in both environments i.e. in VBA you get 'Double' whereas in SQL you
    get 'Decimal'.

    Jamie.

    --
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Jamie Collins wrote:

    > The reason is revealed when you do
    >
    > TYPENAME(3.1 - 3.11 = -0.01)


    Oops, I meant

    TYPENAME(3.1 - 3.11)

    which returns 'Double' in VBA and 'Decimal' in SQL.

    Jamie.

    --
     
  8. hughess7

    hughess7
    Expand Collapse
    Guest

    Thanks tried that also already (formatting calc field to 2 dec in query).
    Normally in a table if you click in a field it changes to show you the
    decimals stored rather than just displayed. In this instance they both appear
    the same and no trailing decimals which is what is confusing me.

    Thanks in advance for any help.
    Sue


    "Jerry Whittle" wrote:

    > The two decimal points are what it shows - not actually what is stored. Also
    > you mentioned a calculated control. Sometimes they do not return exactly what
    > you would expect. Access, and many other programs, have a floating point math
    > problem. Here's an infamous example:
    >
    > Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
    > -0.01.
    >
    > Try formatting the calculated control in the query to show only two
    > decimals. Something like:
    >
    > <> Format([Totals], "0.00")
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "hughess7" wrote:
    >
    > > The stored value is Double, 2 decimals (also tried changing to fixed), the
    > > other comparitive value was in a calculated control in a query definition.
    > >
    > > Just for the purposes of this exercise I created another Totals field in the
    > > table with same field definition but still did not work.
    > >
    > > Very odd...
    > >
    > > --
    > > Thanks in advance for any help.
    > > Sue
    > >
    > >
    > > "Joseph Meehan" wrote:
    > >
    > > > hughess7 wrote:
    > > > ...
    > > > >
    > > > > the
    > > > > data seems to be 2 decimal places
    > > > >
    > > > > Thanks
    > > > > Sue
    > > >
    > > > Access can display data in many different ways and just looking at the
    > > > displayed data is not a good way of determining how Access is actually
    > > > storing or using data. Check the table definition of the field(s) and tell
    > > > us what field types they are.
    > > >
    > > > --
    > > > Joseph Meehan
    > > >
    > > > Dia duit
    > > >
    > > >
    > > >
     
  9. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    hughess7 wrote:
    > Thanks tried that also already (formatting calc field to 2 dec in query).
    > Normally in a table if you click in a field it changes to show you the
    > decimals stored rather than just displayed.


    You should consider changing the data type to DECIMAL(n, 2), where n is
    a suitably large enough value to accommodate values to the left of the
    decimal point. In other words, make it incapable of *storing* any more
    than two decimal places.

    Jamie

    --
     
  10. hughess7

    hughess7
    Expand Collapse
    Guest

    thanks, will give this a try

    Sue

    "Jamie Collins" wrote:

    >
    > hughess7 wrote:
    > > Thanks tried that also already (formatting calc field to 2 dec in query).
    > > Normally in a table if you click in a field it changes to show you the
    > > decimals stored rather than just displayed.

    >
    > You should consider changing the data type to DECIMAL(n, 2), where n is
    > a suitably large enough value to accommodate values to the left of the
    > decimal point. In other words, make it incapable of *storing* any more
    > than two decimal places.
    >
    > Jamie
    >
    > --
    >
    >
     
  11. hughess7

    hughess7
    Expand Collapse
    Guest

    Changing the field types to decimal worked thanks :)

    Sue


    "hughess7" wrote:

    > thanks, will give this a try
    >
    > Sue
    >
    > "Jamie Collins" wrote:
    >
    > >
    > > hughess7 wrote:
    > > > Thanks tried that also already (formatting calc field to 2 dec in query).
    > > > Normally in a table if you click in a field it changes to show you the
    > > > decimals stored rather than just displayed.

    > >
    > > You should consider changing the data type to DECIMAL(n, 2), where n is
    > > a suitably large enough value to accommodate values to the left of the
    > > decimal point. In other words, make it incapable of *storing* any more
    > > than two decimal places.
    > >
    > > Jamie
    > >
    > > --
    > >
    > >
     

Share This Page