Sql convert Numeric to character

[Rick Lipkin]

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: Select all  Expand view

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]

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: Select all  Expand view

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]

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

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

Code: Select all  Expand view

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