Posted by Mike on 10/22/05 17:53

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

[Back to original message]