Problem with Date Range in Having Clause

[ztm]

I have built many queries and sometimes I have this problem and sometimes I don't..I can't seem to figure out what is causing it. Here's an example. Two seperate queries referencing two different forms. Each with user input From and To dates. One works, one reports back zero records. Here is the SQL code from both queries. The one that works is first. Any help would be appreciated. On the second, I can replace the Between([control1] AND [control2]) with >[control1] and it works. I've also tried changing the Having to a Where, no difference.


SELECT Material_Trans.Material, Material_Trans.Vendor, Material_Trans.Material_Trans_Date, Material_Trans.Quantity, Material_Trans.Unit_Cost, [Quantity]*[Unit_Cost] AS [Ext Cost], Material_Trans.Material_Exists
FROM Material_Trans
GROUP BY Material_Trans.Material, Material_Trans.Vendor, Material_Trans.Material_Trans_Date, Material_Trans.Quantity, Material_Trans.Unit_Cost, [Quantity]*[Unit_Cost], Material_Trans.Material_Exists
HAVING (((Material_Trans.Material) Like [Forms]![frmMatCostChange]![cboMaterial]) AND ((Material_Trans.Vendor)<>&quot;ARROWLAB&quot; And (Material_Trans.Vendor)<>&quot;METFIN&quot AND ((Material_Trans.Material_Trans_Date) Between [forms]![frmMatCostChange]![TtxtFromDate] And [forms]![frmMatCostChange]![txtToDate]) AND ((Material_Trans.Unit_Cost)>0) AND ((Material_Trans.Material_Exists)=True))
ORDER BY Material_Trans.Material, Material_Trans.Vendor, Material_Trans.Material_Trans_Date;


SELECT Job_Operation.Work_Center, Job.Part_Number, [job]![job] AS expr2, Job_Operation.Sched_Start, Job_Operation.Operation_Service, Job.Make_Quantity, Job_Operation.Run, Job_Operation.Run_Method, [ActiveParts-BuildRates].RunsPerYr, [Make_Quantity]*[Run] AS extRunTime
FROM ((Job INNER JOIN [ActiveParts-BuildRates] ON Job.Part_Number = [ActiveParts-BuildRates].Part_Number) INNER JOIN Material ON Job.Part_Number = Material.Material) INNER JOIN Job_Operation ON Job.Job = Job_Operation.Job
GROUP BY Job_Operation.Work_Center, Job.Part_Number, [job]![job], Job_Operation.Sched_Start, Job_Operation.Operation_Service, Job.Make_Quantity, Job_Operation.Run, Job_Operation.Run_Method, [ActiveParts-BuildRates].RunsPerYr, [Make_Quantity]*[Run], Material.Status, Job.Type
HAVING (((Job_Operation.Work_Center)=[Forms]![frmWorkcenterJobs]![Combo2]) AND (([job]![job])=[job]![job]) AND ((Job_Operation.Sched_Start) Between [forms]![frmWorkCenterJobs]![txtFromDate] And [forms]![frmWorkCenterJobs]![TxtToDate]) AND ((Material.Status)=&quot;active&quot AND ((Job.Type)<>&quot;blanket&quot)
ORDER BY Job_Operation.Sched_Start DESC , [ActiveParts-BuildRates].RunsPerYr DESC;


Thanks for any help.

 

[fluteplr]

I think you are confused about the difference between 'where' and 'having'.

'Where' works on the records before they are selected and grouped.

'Having' works on the records after they have been selected and grouped and is generally used only for things that did not exist until the grouping, such as count(*), sum(field), max(field) etc.

While using having on fields that existed before the grouping takes place may work it is extremely inefficent.

It also seems that you are grouping on all the selected fields and using no summary functions such as cout, sum, max etc. which makes no sense to me at all.

 

[ztm]

fluteplr, you are exactly right. I was confused on the difference between having and where. I have since taken out the grouping and changed it to a where clause. I have also found out that after formatting my 'txtToDate' control as a short date (like the 'txtFromDate' control was, then my between statement worked. Thanks for the help.