SQL Conversion Question

[AppzDev]

I am running into a problem where i have a dropdown that has 2 variables in it. A greater than 7 and a Less Than 7.

Here is the issue. In the database, due to importing issues, the datatype of the "hgba1c" field is a varchar. When i try to filter out values that are less than 7 however, i get values of 10, 11.2 etc. Here is my SQL syntax for what i'm doing. I guess what i need to know is *HOW* to convert the hgba1c to a decimal, integer, whatever i need in order for this to work.

If ddHGBA1C.SelectedItem.Value = "7" THEN
myValue = "Select pv.pt_id, pv.hgba1c_date, Money(2, pv.hgba1c), pd.pt_lname, pd.pt_fname FROM tblPatient_values pv, tblPatient_Demo pd where pv.pt_id = pd.pt_id AND pv.hgba1c >= '"& ddHGBA1C.SelectedItem.Value & "'" & "AND hgba1c_date BETWEEN " &"'" & mydate & "' AND " &"'" & mydate2 & "' order by hgba1c_date desc"
LoadHGBA1C(myValue)

Else

myValue = &quot;Select pv.pt_id, pv.hgba1c_date, pv.hgba1c, pd.pt_lname, pd.pt_fname FROM tblPatient_values pv, tblPatient_Demo pd where pv.pt_id = pd.pt_id AND pv.hgba1c < '&quot; & cdec(6.9) &&quot;'&quot; '< '&quot;& ddHGBA1C.SelectedItem.Value & &quot;'&quot; '& &quot;AND hgba1c_date BETWEEN &quot; &&quot;'&quot; & mydate & &quot;' AND &quot; &&quot;'&quot; & mydate2 & &quot;' order by hgba1c_date desc&quot;
LoadHGBA1C(myValue)
End If

This statement hits another sub which actually binds a datagrid etc etc.

Any and all help is greatly appreciated.

dc~

 

[wsmall73]

SQL Syntax would be CONVERT(Decimal(18,2),yourvarcharfield)

 

[checkai]

If ddHGBA1C.SelectedItem.Value = &quot;7&quot; THEN
myValue = &quot;Select pv.pt_id, pv.hgba1c_date, convert(int,Money(2, pv.hgba1c))as hgba1c, pd.pt_lname, pd.pt_fname FROM tblPatient_values pv, tblPatient_Demo pd where pv.pt_id = pd.pt_id AND pv.hgba1c >= '&quot;& ddHGBA1C.SelectedItem.Value & &quot;'&quot; & &quot;AND hgba1c_date BETWEEN &quot; &&quot;'&quot; & mydate & &quot;' AND &quot; &&quot;'&quot; & mydate2 & &quot;' order by hgba1c_date desc&quot;
LoadHGBA1C(myValue)

Else

myValue = &quot;Select pv.pt_id, pv.hgba1c_date, convert(int,pv.hgba1c)as hgba1c, pd.pt_lname, pd.pt_fname FROM tblPatient_values pv, tblPatient_Demo pd where pv.pt_id = pd.pt_id AND pv.hgba1c < '&quot; & cdec(6.9) &&quot;'&quot; '< '&quot;& ddHGBA1C.SelectedItem.Value & &quot;'&quot; '& &quot;AND hgba1c_date BETWEEN &quot; &&quot;'&quot; & mydate & &quot;' AND &quot; &&quot;'&quot; & mydate2 & &quot;' order by hgba1c_date desc&quot;
LoadHGBA1C(myValue)
End If