Professional Web Applications Themes

Update a currency field in Access - Coldfusion Database Access

When I enter .50 on the form the results in the database is 1.00. If I change 1.00 to 2.00 the change is sent to the database. The problem seems to be that the database is rounding the field. For this particular application the user needs to be able to enter amounts as small as .25. The field in the database is set to currency with 2 decimal places. The update code follows. Thank you in advance for your help. UPDATE customer_info SET fee_paid = <cfqueryparam value="#form.fee_paid#" cfsqltype="cf_sql_bit" />, fee_amount = <cfqueryparam value="#form.fee_amount#" cfsqltype="cf_sql_numeric" />, receipt = <cfqueryparam value="#form.receipt#" cfsqltype="cf_sql_bit" ...

Sponsored Links
  1. #1

    Default Update a currency field in Access

    When I enter .50 on the form the results in the database is 1.00. If I change
    1.00 to 2.00 the change is sent to the database. The problem seems to be that
    the database is rounding the field. For this particular application the user
    needs to be able to enter amounts as small as .25. The field in the database
    is set to currency with 2 decimal places.

    The update code follows. Thank you in advance for your help.




    UPDATE customer_info
    SET fee_paid = <cfqueryparam value="#form.fee_paid#" cfsqltype="cf_sql_bit"
    />,
    fee_amount = <cfqueryparam value="#form.fee_amount#"
    cfsqltype="cf_sql_numeric" />,
    receipt = <cfqueryparam value="#form.receipt#" cfsqltype="cf_sql_bit" />,
    staff_lname = <cfqueryparam value="#form.staff_lname#"
    cfsqltype="cf_sql_varchar" />,
    staff_fname = <cfqueryparam value="#form.staff_fname#"
    cfsqltype="cf_sql_varchar" />,
    staff_ph_ext = <cfqueryparam value="#form.staff_ph_ext#"
    cfsqltype="cf_sql_varchar" />
    WHERE cust_id = <cfqueryparam value="#form.cust_id#"
    cfsqltype="cf_sql_numeric" />

    Sponsored Links
    margo_ann Guest

  2. #2

    Default Re: Update a currency field in Access

    try using cf_sql_decimal for a cfsqltype in your cfqueryparam tag.
    cf_sql_numeric doesn't do decimals.
    HTH
    --
    Tim Carley
    www.recfusion.com
    com
    Mountain Guest

  3. #3

    Default Re: Update a currency field in Access

    Change the datatype from currency to single.
    Dan Guest

  4. #4

    Default Re: Update a currency field in Access

    I changed the cfqueryparam tag as follows:

    fee_amount = <cfqueryparam value="#form.fee_amount#"
    cfsqltype="cf_sql_decimal" />,

    I thought this would sovle the probme, however, I receive this error now:

    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver]Optional feature not implemented

    The error occurred in
    C:\Inetpub\wwwroot\spl_web_site\public_records\sta ff_forms\update.cfm: line 33

    31 : staff_fname = <cfqueryparam value="#form.staff_fname#"
    cfsqltype="cf_sql_varchar" />,
    32 : staff_ph_ext = <cfqueryparam value="#form.staff_ph_ext#"
    cfsqltype="cf_sql_varchar" />
    33 : WHERE cust_id = <cfqueryparam value="#form.cust_id#"
    cfsqltype="cf_sql_numeric" />
    34 : </cfquery>
    35 :
    Now there seems to be an error on the cust_id field which is an auto number in
    the database.



    margo_ann Guest

  5. #5

    Default Re: Update a currency field in Access

    in line 33, change the cfsqltype from numeric to integer.
    Dan Guest

  6. #6

    Default Re: Update a currency field in Access

    I changed line 33 to cfsqltype="cf_sql_integer" and now the field in the database seems to drop the cents. If I enter 1.50 or 1.75 the database only shows 1.00.
    margo_ann Guest

  7. #7

    Default Re: Update a currency field in Access

    make sure you specify the scale for the cf_sql_decimal. In your case you
    want a scale=2
    HTH
    --
    Tim Carley
    www.recfusion.com
    com
    Mountain Guest

  8. #8

    Default Re: Update a currency field in Access

    I am not sure what you mean when you refer to scale. Can you clarify or give an example.
    margo_ann Guest

  9. #9

    Default Re: Update a currency field in Access

    http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm
    --
    Tim Carley
    www.recfusion.com
    com
    Mountain Guest

Similar Threads

  1. Update Access field, SQL operator
    By Frec in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 5th, 05:40 PM
  2. Access Database Currency
    By GabXav1852 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: July 19th, 09:44 AM
  3. How do I view currency field in format $xx.xx
    By Terry Murray in forum ASP Database
    Replies: 2
    Last Post: November 10th, 10:01 PM
  4. field name with special currency symbol
    By xixi in forum IBM DB2
    Replies: 0
    Last Post: September 10th, 07:51 PM
  5. Replies: 5
    Last Post: July 22nd, 07:32 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139