One possible solution is to create a view in the database using the select statement you are showing above. Then change the select statement in your application to select from the view. But you will not need the COUNT(*) in your application because it will be in the view. So all your application will see is the field names without an alias.
Another possible solution is to re-write the select in your application like this
SELECT DEPARTMENT, SEX, TOTALPATIENTS
FROM (SELECT DEPARTMENT, SEX, COUNT(*) AS TotalPatients
FROM PATIEnts
GROUP BY DEPARTMENT, SEX)
ORDER BY DEPARTMENT;
Another possible solution is that the VB6 data report is case sensitive. So you would either need to change the alias to "AS TOTALPATIENTS" or change the rptlabel to "TotalPatients".