topic-29163

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Sql convert Numeric to character
Posted: Fri Aug 29, 2014 03:06 PM

To All

I have successfully written a Sql script that takes the Sql Field [InvoiceNumber] Integer and converted it to character with this script and it works :

Code (fw): Select all Collapse

cSql := "Select str(InvoiceNumber) as InvoiceNumber, " 
cSql += "CustomerId "
cSql += "From Billing"


oRsInv := TOleAuto():New( "ADODB.Recordset" )
oRsInv:CursorType     := 1        // opendkeyset
oRsInv:CursorLocation := 3        // local cache
oRsInv:LockType       := 3        // lockoportunistic

TRY
   oRsInv:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening Invoice Number table" )
   oRsCust:Close()
   oRsBill:Close()
   _CleanUP()
   oDlg:End()
   RETURN(.F.)
END TRY

xbrowse(oRsInv )

oRsInv:Filter := "InvoiceNumber = '10'"
xbrowse( oRsInv )

As you can see .. [InvoiceNumber] has been converted to Charactor .. however, creating a filter on the converted [InvoiceNumber] fails ???

Any Ideas ?
Rick Lipkin

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Sql convert Numeric to character ( solved )
Posted: Fri Aug 29, 2014 03:35 PM

To All

With a bit more research I found this Sql function cStr() to give me the correct results in Ms Access ..

http://bytes.com/topic/access/answers/2 ... ger-string

Code (fw): Select all Collapse

cSql := "Select cStr([InvoiceNumber]) as InvoiceNumber, " // as Invoicenumber, "
cSql += "CustomerId "
cSql += "From Billing"

I do know know if cStr() is compatible with Sql Server ?

Thanks
Rick Lipkin

Biel EA6DD

Posts: 682

Joined: Tue Feb 14, 2006 09:48 AM

Re: Sql convert Numeric to character
Posted: Mon Sep 01, 2014 06:40 AM

STR ( float_expression [ , length [ , decimal ] ] )

I thing the default length returned by STR is 10. Try to specify a lengt.

Code (fw): Select all Collapse

Select str(InvoiceNumber,2) as InvoiceNumber, "
...
oRsInv:Filter := "InvoiceNumber = '10'"

Saludos desde Mallorca
Biel Maimó
http://bielsys.blogspot.com/

FiveTech Support Forums

Continue the discussion