Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting data in a SQL view

    I am having problems formatting data that I am gathering from a view in SQL Server. I have an amount field that I am attempting to add commas to, but I am unable to add them. I have attempted to convert the data to Money, small money, float and decimal and I have not been successful. Please see my code below, any help would be greatly appreciated.

    SELECT TOP 100 PERCENT isgwen.applview_DlyGrtThen_Union_Step2.AgentNbr AS [Agent Number], dbo.tbl_PrimRepstats.Primagtnam AS [Rep Name],
    isgwen.applview_DlyGrtThen_Union_Step2.PolicyAcct AS [Policy Number],
    isgwen.applview_DlyGrtThen_Union_Step2.Description AS [Product Description],
    isgwen.applview_DlyGrtThen_Union_Step2.ProductGrp AS [Product Group], CAST(isgwen.applview_DlyGrtThen_Union_Step2.Amount AS decimal(38, 2))
    AS Amount, isgwen.applview_DlyGrtThen_Union_Step2.LOB, dbo.tblDOM_Channel.ChnlRptHeading AS Channel,
    dbo.tblDOM_MgrTbl.Mgrname AS Manager, dbo.tbl_RepAddress.RepPhoneNbr, dbo.tbl_RepAddress.RepEmail,
    dbo.tblDOM_Dvp.DvpRegName AS Reg
    FROM isgwen.applview_DlyGrtThen_Union_Step2 INNER JOIN
    dbo.tbl_RepAddress INNER JOIN
    dbo.tblDOM_Primagt INNER JOIN
    dbo.tbl_PrimRepstats ON dbo.tblDOM_Primagt.Primary# = dbo.tbl_PrimRepstats.PrimAgtNbr ON
    dbo.tbl_RepAddress.RepNbr = dbo.tbl_PrimRepstats.PrimAgtNbr ON
    isgwen.applview_DlyGrtThen_Union_Step2.AgentNbr = dbo.tblDOM_Primagt.Agent# INNER JOIN
    dbo.tblDOM_Channel ON isgwen.applview_DlyGrtThen_Union_Step2.ChanlID = dbo.tblDOM_Channel.ChanlID INNER JOIN
    dbo.tblDOM_MgrTbl INNER JOIN
    dbo.tblDOM_Dvp ON dbo.tblDOM_MgrTbl.DvpID = dbo.tblDOM_Dvp.DvpID ON
    dbo.tbl_PrimRepstats.TerrMgaID = dbo.tblDOM_MgrTbl.TerrMgaID
    GROUP BY isgwen.applview_DlyGrtThen_Union_Step2.AgentNbr, dbo.tbl_PrimRepstats.Primagtnam, isgwen.applview_DlyGrtThen_Union_Step2.PolicyAcct,
    isgwen.applview_DlyGrtThen_Union_Step2.Description , isgwen.applview_DlyGrtThen_Union_Step2.ProductGrp,
    isgwen.applview_DlyGrtThen_Union_Step2.Amount, dbo.tblDOM_Channel.ChnlRptHeading, dbo.tblDOM_MgrTbl.Mgrname,
    isgwen.applview_DlyGrtThen_Union_Step2.LOB, dbo.tblDOM_Dvp.DvpRegName, dbo.tbl_RepAddress.RepPhoneNbr, dbo.tbl_RepAddress.RepEmail

    Thanks,

    Vbkerk

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Formatting data in a SQL view

    I presume from your table names that you are using an Access front-end linked to SQL Server tables via ODBC. In that case, you can use the format command to produce a specific format for the Amount field, but you don't need to mess with the SQL View unless you really want to - in any event if its a monetary field, then it should be defined as Money in SQL Server. Another way to do the same thing is to apply a Currency Format to the field containing the amount - you do that using the Query Designer view in Access. Or you could create your own format string and use that.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting data in a SQL view

    I am actually using a VB 6.0 application as the front end and bring the data into a Flex H Grid with an ADO control.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Formatting data in a SQL view

    Unfortunately I have no experience with either the Flex H Grid or using ADO from VB. I think you really probably want to do the formatting in SQL Server - I don't have the Books Online at my disposal at the moment, but it seems to me there was a formating function available to do this kind of thing. However you would probably need to change your view to a Stored Procedure to use the function. And that may be a problem if you want users to be able to update data. I you don't get any further responses, we could try moving this thread to the VB forum.
    Wendell

Posting Permissions

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