Results 1 to 3 of 3
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Append Query with Formatted Output - Two Decimal Points

    I'm using SQL Server 2008 R2 as backend database. I need to append a row to a table where the destination field is text (nvarchar) and the source field is float. The source field may contain zero, 1, or 2 decimal places. I need the destination, text field to always show the two decimal places, i.e., 12.3 expands to 12.30. If I write the Access query directly in Query Builder, I can designate the properties of the output field as fixed, 2 decimal places. However, in this case I need to generate the SQL code for the query dynamically in VBA, save it, and execute it. Is there a way I can tweak the properties of the output field in VBA to fixed, 2 decimals before running it, as if I had done this in query builder design mode?



    Thanks, Eddie

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you know about the Format property?
    Try something like

    Format([YourFieldName],"##,##0.00")

    Code:
    SELECT Table1.Name1, Format([YourFieldName],"##,##0.00") AS Expr1 FROM Table1


    You might also have to force a data type conversion.... use the CStr() function.

    CStr(Format([YourFieldName],"##,##0.00"))

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    That worked. I wasn't familiar with the Format property. By the way, I did not have to use CStr() function.

    Eddie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-14-2012, 01:37 AM
  2. Query to Formatted Excel Sheet
    By pbaker36 in forum Programming
    Replies: 4
    Last Post: 04-17-2012, 03:28 PM
  3. Replies: 6
    Last Post: 01-15-2012, 11:07 PM
  4. Replies: 5
    Last Post: 12-10-2011, 11:49 AM
  5. Formatted Excel Output
    By jerryrs in forum Import/Export Data
    Replies: 6
    Last Post: 02-26-2011, 11:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums