CAST Command

I am using MS-Access as a front end for my MS-SQL DB. I have a sql view that
uses the following:

SELECT     TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS [
Service
Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
BILLDATE AS [Bill Date], CHKAMT AS [Check Amt], PSDATE
AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code]
,
TRLR AS [T Code Desc],  SUBSTRING(CAST(SVCCO
DE AS
varchar), 1, 4) AS [Dept #]
FROM         dbo.PAT_Transactions
ORDER BY PATNUMBER, SVCCODE

My problem is the cast command. Will this sql view works and cast the
SVCCODE field into a varchar, I need to cast the reseult of  this,
 SUBSTRING(CAST(SVCCO
DE AS varchar), 1, 4) AS [Dept #], back in to a deci
mal
format. I am dropping this view onto a form and need to link to a field on
the form that is in decimal format.

The only way I could get this to work was to create another view, based on
the one above, and cast the [Dept #] field back into the decimal format.
 Is
there any better way to do this? Can I nest the Cast command?

Hope this makes sense.

Thanks in advance.

Mike
m charney at dunlap hospital dot org

Like this:

 CAST(SUBSTRING(CAST(
svccod AS VARCHAR), 1, 4) AS DECIMAL(4,0))

--
David Portas
SQL Server MVP
--

"Mike" <noway@forgetit.com> wrote in message
news:qns6f.1047$jV2.300@newssvr17.news.prodigy.com...
>I am using MS-Access as a front end for my MS-SQL DB. I have a sql view
>that uses the following:
>
> SELECT     TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS
> [Service Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
>                      BILLDATE AS [Bill Date], CHKAMT AS [Check Amt
],
> PSDATE AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS 	
1;T Code],
>                      TRLR AS [T Code Desc],  SUBSTRING(CAST(SVCCO
DE AS
> varchar), 1, 4) AS [Dept #]
> FROM         dbo.PAT_Transactions
> ORDER BY PATNUMBER, SVCCODE
>
> My problem is the cast command. Will this sql view works and cast the
> SVCCODE field into a varchar, I need to cast the reseult of  this,
>  SUBSTRING(CAST(SVCCO
DE AS varchar), 1, 4) AS [Dept #], back in to a
> decimal format. I am dropping this view onto a form and need to link to a
> field on the form that is in decimal format.
>
> The only way I could get this to work was to create another view, based on
> the one above, and cast the [Dept #] field back into the decimal forma
t.
> Is there any better way to do this? Can I nest the Cast command?
>
> Hope this makes sense.
>
> Thanks in advance.
>
> Mike
> m charney at dunlap hospital dot org
>
>
>
>

Mike (noway@forgetit.com)  writes:
> My problem is the cast command. Will this sql view works and cast the
> SVCCODE field into a varchar, I need to cast the reseult of  this,
>  SUBSTRING(CAST(SVCCO
DE AS varchar), 1, 4) AS [Dept #], back in to a
> decimal format. I am dropping this view onto a form and need to link to
> a field on the form that is in decimal format.
>
> The only way I could get this to work was to create another view, based
> on the one above, and cast the [Dept #] field back into the decimal
> format. Is there any better way to do this? Can I nest the Cast
> command?

Yes, you can nest:

 CAST(SUBSTRING(CAST(
SVCCODE AS varchar), 1, 4) AS decimal(p, s))




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks!!


"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
 news:GtydncJkzuT17Mf
eRVnyjQ@giganews.com...
> Like this:
>
>  CAST(SUBSTRING(CAST(
svccod AS VARCHAR), 1, 4) AS DECIMAL(4,0))
>
> --
> David Portas
> SQL Server MVP
> --
>
> "Mike" <noway@forgetit.com> wrote in message
> news:qns6f.1047$jV2.300@newssvr17.news.prodigy.com... 
>
>