By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,954 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,954 IT Pros & Developers. It's quick & easy.

CAST Command

P: n/a
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(SVCCODE 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(SVCCODE 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?

Hope this makes sense.

Thanks in advance.

Mike
m charney at dunlap hospital dot org


Oct 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Like this:

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

--
David Portas
SQL Server MVP
--

"Mike" <no***@forgetit.com> wrote in message
news:qn****************@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 [T Code],
TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE 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(SVCCODE 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?

Hope this makes sense.

Thanks in advance.

Mike
m charney at dunlap hospital dot org

Oct 22 '05 #2

P: n/a
Mike (no***@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(SVCCODE 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, es****@sommarskog.se

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

Oct 22 '05 #3

P: n/a
Thanks!!
"David Portas" <RE****************************@acm.org> wrote in message
news:Gt********************@giganews.com...
Like this:

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

--
David Portas
SQL Server MVP
--

"Mike" <no***@forgetit.com> wrote in message
news:qn****************@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 [T Code],
TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE 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(SVCCODE 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?

Hope this makes sense.

Thanks in advance.

Mike
m charney at dunlap hospital dot org


Oct 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.