Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: SQL to Access - field format problem

    I have two tables in MS Access that are linked to SQL tables in a Data Warehouse.

    In one of the Access tables, the field Quantity is defined as ‘text”.

    On my report the field is displayed as 526995.000 but should read 3,479,970

    Access will not let me change the field properties because the table is linked to the SQL table.

    Is there any way within Access to “convert” the field so it displays correctly?

    Thanks,

    Krazy

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try using an expression such as =CStr([MyField]) in the control source for the textbox field. It will give you an error though if the value is null so you may have to put an iif in the clause (although I think someone might have a better suggestion than using CStr.)
    Last edited by pkstormy; 04-04-08 at 15:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    The field name is: Quantity
    Using =CStr([Quantity]) in the the control souce for the field.

    The report shows "Error 0" for every instance.
    I also got a message saying "This control has a reference to itself".

    I think your suggestion is on the right tract but I don't know where to take it from here. Appreciate your help.

    Krazy

  4. #4
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    Just an update.
    I tried several other "functions" (Cstr$, Count, LCase$, LCase, etc).
    I can "Count" and the counted number appears corectly on the report.
    However, that doesn't help as I need to convert the field to a number and "Sum" it.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try using the expression in a column in the recordsource query for the report. You'll need to do that to sum the field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you try CLng() or CCur()? To get rid of the error, change the name of the textbox in the report to txtQuantity so that it's different than the field name.
    Paul

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ CCur is what I would first try.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Keep in mind that you'll need to use the expression in a recordsource query if you want to sum on that field in the report. You cannot sum on an expression field in a report.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or a form btw
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Thanks to all

    I was able to use the SUM command in my query. That allowed me to then change the format. Thank you all for your suggestions.

    Krazy

Posting Permissions

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