I have a table (Manhours) with 4 fields (ManID, VehicleID, Manhours and RptDate). I created the query (query 1) below (in Access 2000) to sum the total hours for each vehicle. It works OK in Access 2000.
This query is also used by an application written in VB6 (using Crystal Reports) to create a report. The report only has 2 fields (Vehicle and Total manhours). But when I run the report I get a "No data Found" message from Crystal.
Query 1
"SELECT Sum(manhours.Manhours) AS SumYearHours, Vehicles.Vehicle
FROM manhours INNER JOIN Vehicles ON manhours.VehicleID = Vehicles.VehicleID
WHERE (((manhours.RptDate) Like "*")
GROUP BY Vehicles.Vehicle, manhours.VehicleID"
I also use the query below (in the same report).
Query 2
"SELECT Sum(manhours.Manhours) AS SumYearHours, Vehicles.Vehicle FROM manhours INNER JOIN Vehicles ON manhours.VehicleID = Vehicles.VehicleID WHERE (manhours.rptDate >= #Format(DTShowStartDate, "yyyy/mm/dd"# AND Manhours.rptDate <= #Format(DTShowEndDate, "yyyy/mm/dd"#) GROUP BY Vehicles.Vehicle, manhours.VehicleID"
This allows me to select start and end dates via a DTPicker in VB. This query works fine. Can someone please tell me why query 1 does not work with crystal - almost as if it does not like the "*", but then how do I sum the hours. If I leave the "(((manhours.RptDate) Like "*")" out of query1 I get a listing of individual entries in crystal. To me this confirms that the connection etc is working - only crystal does not like it when I include the dates.
This query is also used by an application written in VB6 (using Crystal Reports) to create a report. The report only has 2 fields (Vehicle and Total manhours). But when I run the report I get a "No data Found" message from Crystal.
Query 1
"SELECT Sum(manhours.Manhours) AS SumYearHours, Vehicles.Vehicle
FROM manhours INNER JOIN Vehicles ON manhours.VehicleID = Vehicles.VehicleID
WHERE (((manhours.RptDate) Like "*")
GROUP BY Vehicles.Vehicle, manhours.VehicleID"
I also use the query below (in the same report).
Query 2
"SELECT Sum(manhours.Manhours) AS SumYearHours, Vehicles.Vehicle FROM manhours INNER JOIN Vehicles ON manhours.VehicleID = Vehicles.VehicleID WHERE (manhours.rptDate >= #Format(DTShowStartDate, "yyyy/mm/dd"# AND Manhours.rptDate <= #Format(DTShowEndDate, "yyyy/mm/dd"#) GROUP BY Vehicles.Vehicle, manhours.VehicleID"
This allows me to select start and end dates via a DTPicker in VB. This query works fine. Can someone please tell me why query 1 does not work with crystal - almost as if it does not like the "*", but then how do I sum the hours. If I leave the "(((manhours.RptDate) Like "*")" out of query1 I get a listing of individual entries in crystal. To me this confirms that the connection etc is working - only crystal does not like it when I include the dates.