asked on
Convert number to string in SQL statement
This is my Sql statement which appears on a datareport in vb6. In the database, paymentmethodID is formatted as a number, yet it appears in the database as a string not a number. So when I add PaymentMethodID from Payments it should show Cash or Check or some text. It shows 1 2 3 etc.
How can I convert it in the sql statment or format it so that it shows the string. See second APPEND statement at the end of the sql statement below: It is an Access 2002 database backend.
--------------------------
sql = "SHAPE {SELECT CustomerID, CompanyName, CCompanyName, (ContactTitle & ' ' & ContactFirstName & ' ' & ContactLastName) As ContactName, (CContactTitle & ' ' & CContactFirstName & ' ' & CContactLastName) As CContactName, (BillingAddress & ' ' & IIF(IsNull(City),'',City & ', ') & IIF(IsNull(StateOrProvince
"APPEND (( SHAPE {SELECT TotParts.DateFinished, TotParts.DatePickedUp, TotParts.WorkorderID, TotParts.CalledInBy, WOTotal.WOTotal AS Total, TotParts.CustomerID, TotParts.Labor AS LaborTotal, TotParts.Parts AS PartTotal, WOTaxTotal.WOTaxTot AS TaxTotal, ((TotParts.Parts + TotParts.Labor + WOTaxTotal.WOTaxTot) - TotParts.TotPayments) AS AmountDue FROM WOTotal, TotParts, WOTaxTotal WHERE ((TotParts.DatePickedUp) BETWEEN #" & DateRange & "# AND #" & DateRangeEnd & "#) AND WOTotal.WorkorderID = TotParts.WorkorderID AND TotParts.WorkorderID = WOTaxTotal.WorkorderID} AS Workorder " & _
"APPEND ({SELECT WorkorderID, PaymentID, PaymentDate, PaymentAmount FROM Payments} AS Payment RELATE 'WorkorderID' TO 'WorkorderID') AS Payment) AS Workorder RELATE 'CustomerID' TO 'CustomerID') AS Workorder"
How can I convert it in the sql statment or format it so that it shows the string. See second APPEND statement at the end of the sql statement below: It is an Access 2002 database backend.
--------------------------
sql = "SHAPE {SELECT CustomerID, CompanyName, CCompanyName, (ContactTitle & ' ' & ContactFirstName & ' ' & ContactLastName) As ContactName, (CContactTitle & ' ' & CContactFirstName & ' ' & CContactLastName) As CContactName, (BillingAddress & ' ' & IIF(IsNull(City),'',City & ', ') & IIF(IsNull(StateOrProvince
"APPEND (( SHAPE {SELECT TotParts.DateFinished, TotParts.DatePickedUp, TotParts.WorkorderID, TotParts.CalledInBy, WOTotal.WOTotal AS Total, TotParts.CustomerID, TotParts.Labor AS LaborTotal, TotParts.Parts AS PartTotal, WOTaxTotal.WOTaxTot AS TaxTotal, ((TotParts.Parts + TotParts.Labor + WOTaxTotal.WOTaxTot) - TotParts.TotPayments) AS AmountDue FROM WOTotal, TotParts, WOTaxTotal WHERE ((TotParts.DatePickedUp) BETWEEN #" & DateRange & "# AND #" & DateRangeEnd & "#) AND WOTotal.WorkorderID = TotParts.WorkorderID AND TotParts.WorkorderID = WOTaxTotal.WorkorderID} AS Workorder " & _
"APPEND ({SELECT WorkorderID, PaymentID, PaymentDate, PaymentAmount FROM Payments} AS Payment RELATE 'WorkorderID' TO 'WorkorderID') AS Payment) AS Workorder RELATE 'CustomerID' TO 'CustomerID') AS Workorder"
Visual Basic ClassicSQL
Last Comment
c0ldfyr3