Posted by Mike on 10/22/05 21:13

Thanks!!


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:GtydncJkzuT17MfeRVnyjQ@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...
>>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]