Need Help on ADODB Query, need to convert field to int and order on it

[brusk]

Guys, having an issue trying to get my query to order properly. Below is the working query but the problem is it's pulling the [Row] field in as text instead of an int so when it orders it it's not numerically correct. I've tried various things all of which gave me errors.

Call RunSQLQuery(ThisWorkbook.Name, "SELECT * FROM " & _                        "(SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[HD3Drive], HW.[HD3Role], HW.[HD3Size], HW.[HD3SP], HW.[HD4Drive], HW.[HD4Role], HW.[HD4Size], HW.[HD4SP], HW.[HD5Drive], HW.[HD5Role], HW.[HD5Size], HW.[HD5SP], HW.[HD6Drive], HW.[HD6Role], HW.[HD6Size], HW.[HD6SP], HW.[HD7Drive], HW.[HD7Role], HW.[HD7Size], HW.[HD7SP], HW.[HD8Drive], HW.[HD8Role], HW.[HD8Size], HW.[HD8SP], [B]HW.[Row][/B] FROM [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Network") & "] NW, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND [B]CInt(HW.[Row]) = CInt(NW.[Row][/B]) [B]ORDER BY S.[Alias], HW.[Row])[/B] UNION " & _
                        "( SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[HD3Drive], HW.[HD3Role], HW.[HD3Size], HW.[HD3SP], HW.[HD4Drive], HW.[HD4Role], HW.[HD4Size], HW.[HD4SP], HW.[HD5Drive], HW.[HD5Role], HW.[HD5Size], HW.[HD5SP], HW.[HD6Drive], HW.[HD6Role], HW.[HD6Size], HW.[HD6SP], HW.[HD7Drive], HW.[HD7Role], HW.[HD7Size], HW.[HD7SP], HW.[HD8Drive], HW.[HD8Role], HW.[HD8Size], HW.[HD8SP],[B] HW.[Row][/B] FROM [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW, [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTableAVPNetwork") & "] NW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND [B]CInt(HW.[Row]) = CInt(NW.[Row])[/B]) [B]ORDER BY S.[Alias], HW.[Row][/B]", arResults, True, False)

Thanks,
Bruce

 

[Worf]

Someone may need this:

https://stackoverflow.com/questions...-convert-string-to-double-in-select-statement