Convert Srting to Integer inside SQL

[Joan Josep]

Hi all,

I have a question, I have a string type fiel, (ex. "3") how can I get it in an SQL phrase and returned as an integer?

Can I do this??

SQLPhrase = "SELECT VAL(THE_FIELD) FROM MYTABLE WHERE ..."


...

Thanks in advance.

[SteveFlitIII]

Yes.
You can use most conversion methods that you can in VB. So Val, Cint, Cstr etc etc.

[Joan Josep]

Ok thanks, now I have a problem, when I execute the query it returns no data (null) for all the records on this field whith the val() what hapens if one value is "a" instead of "3" ? It returns Null? or fails all the query?

Instead I have a FlexGrid associated with a data control, and the column of THE_FIELD shows no data (like null's)

Any Idea??

[SteveFlitIII]

It should not return Null as the Val of any string or that contains an alphabetic character is always 0 (zero)
What is the exact sql query you are using for the connection string for the data control?

I would execute the sql and just get the original value. THEN, do your conversion; i.e., do the VAL(FIELD_NAME) later. If you are populating a grid, do the conversion as you cycle through the recordset, rather than WHEN you get the recordset.

HTH

[Joan Josep]

Initially I have no code, only a Data control and a DBGrid associated to the data control.

I have a command button that has to sort the recordset by a field and this is the code that sorts the fields:

Referencias is the name of the data control (access db)
the field whith text values is PVP_PTS and PVP_EUR


code:

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComCtlLib.Button)
Dim Column
On Error Resume Next
Select Case DBGrid1.SelStartCol
Case 0
Column = "ID_REF"
Case 1
Column = "FECHA_ALTA"
Case 2
Column = "DESCRIPCION"
Case 3
Columna = "val(PVP_PTS)"
Case 4
Columna = "val(PVP_EUR)"
End Select

Select Case Button.Key
Case "Orden ascding"
Referencias.RecordSource = "SELECT REFERENCIAS.ID_REF, REFERENCIAS.FECHA_ALTA, " & _
"TIPOS_OPERACION.DESCRIPCION, tipo.Tipocast, REFERENCIAS.CALLE, REFERENCIAS.NUMERO, " & _
"REFERENCIAS.POBLACION, REFERENCIAS.[ATICO?], REFERENCIAS.M2_UTIL, " & _
"REFERENCIAS.NUM_HABIT, REFERENCIAS.NUM_BAÑOS, REFERENCIAS.NUM_ASEOS, " & _
" val(REFERENCIAS.PVP_PTS), val(REFERENCIAS.PVP_EUR) FROM TIPOS_OPERACION RIGHT " & _
"JOIN (tipo RIGHT JOIN REFERENCIAS ON tipo.tipo = REFERENCIAS.TIPO_INMUEBLE) " & _
"ON TIPOS_OPERACION.TIPO_OPERACION = REFERENCIAS.TIPO_OPERACION ORDER BY " & Column
Case "Orden desc"
Referencias.RecordSource = "SELECT REFERENCIAS.ID_REF, REFERENCIAS.FECHA_ALTA, " & _
"TIPOS_OPERACION.DESCRIPCION, tipo.Tipocast, REFERENCIAS.CALLE, REFERENCIAS.NUMERO, " & _
"REFERENCIAS.POBLACION, REFERENCIAS.[ATICO?], REFERENCIAS.M2_UTIL, " & _
"REFERENCIAS.NUM_HABIT, REFERENCIAS.NUM_BAÑOS, REFERENCIAS.NUM_ASEOS, " & _
"val(REFERENCIAS.PVP_PTS), val(REFERENCIAS.PVP_EUR) FROM TIPOS_OPERACION RIGHT " & _
"JOIN (tipo RIGHT JOIN REFERENCIAS ON tipo.tipo = REFERENCIAS.TIPO_INMUEBLE) " & _
"ON TIPOS_OPERACION.TIPO_OPERACION = REFERENCIAS.TIPO_OPERACION ORDER BY " & Column & " DESC"
End Select

Referencias.Refresh
End Sub

Your method for accessing and converting breaks the rule of performing one task only at each step..You sould follow the advice given by PanamaAU as it should be done in seperate steps..wether you choose to convert then retrieve or retrieve then convert is your choice but whenever more than one method is performed at the same time it becomes difficult to error test. Your error tests might include tests for null and testing against ascii for proper number letter combinations.

[Joan Josep]

OK Thanks to everybody, buy finally I found the solution,

The Problem was:

If I put " Val(my_field) " I MUST put the clause AS my_field after the val(my_field) the problem dont where nulls, the recordset didn't contain the field my_field


Thanks.