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

[brusk]

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.[Row] 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 CInt(HW.[Row]) = CInt(NW.[Row]) ORDER BY S.[Alias], HW.[Row]) 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.[Row] 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 CInt(HW.[Row]) = CInt(NW.[Row])) ORDER BY S.[Alias], HW.[Row]", arResults, True, False)

[Blade Hunter]

You are sorting on S.[Alias] first. Get rid of the order by in the first part of the union as you are sorting post union.

What DB is this? Access maybe?

[brusk]

Yes sorting by Alias and then Row. It's all within excel, which has been very temperamental when it comes to ADODB queries. I'll try taking out the first order and see what happens, currently it all works it's just sorting Row as text instead of numberic so it's order 1, 10, 11, 2, 3, 4 etc.

[brusk]

Look like I got it now.

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], CInt(HW.[Row]) As RowNum 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 CInt(HW.[Row]) = CInt(NW.[Row])) 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], CInt(HW.[Row]) As RowNum 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 CInt(HW.[Row]) = CInt(NW.[Row])) ORDER BY S.[Alias], RowNum", arResults, True, False)

[Blade Hunter]

Cool, the other thing I was going to suggest is format rownum as a string and make it Right("000" & RowNum,4)