Problem with a query

[WLC]

I have the following query:

SELECT DISTINCT tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.[From BA_REPORT], tblMaster.Hour_Ending, Sum(tblMaster.Import) AS SumOfImport, Sum(tblMaster.Export) AS SumOfExport, Sum(([Export]-[Import])) AS Net, Sum((IIf(IsNull([OffPeakHour]) And IsNull([OffPeakDay]),([export]-[import])))) AS OnPeak, Sum((IIf([OffPeakHour]="OffPeakHour" Or [OffPeakDay]="OffPeakDay",([export]-[import])))) AS OffPeak
FROM tblMaster
GROUP BY tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.[From BA_REPORT], tblMaster.Hour_Ending
HAVING (((tblMaster.OperatingDay)=[Forms]![frm03HourlyNAITotalsEIS]![Combo13]) AND ((tblMaster.[TO BA_REPORT])=[Forms]![frm03HourlyNAITotalsEIS]![Combo3]) AND ((tblMaster.[From BA_REPORT])=[Forms]![frm03HourlyNAITotalsEIS]![Combo49]));

When I run it as a query it runs fine (I get the intended results). But when I copy this same SQL into the RecordSource of a form (the same form mentioned above frm03HourlyNAITotalsEIS) I get no results).

Any idea why?

 

[plog]

Your query is based what's in the form but your form is based on what's in the query but your query is based what's in the form but your form is based on what's in the query, but your...etc.

The chicken can't come before the egg, nor vice versa.

 

[WLC]

I've used this technique successfully before. Just can't quite figure out what's wrong with this one.

Here is one that works (populates my form):

SELECT DISTINCT tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.Hour_Ending, Sum(tblMaster.Import) AS SumOfImport, Sum(tblMaster.Export) AS SumOfExport, Sum(([Export]-[Import])) AS Net, Sum((IIf(IsNull([OffPeakHour]) And IsNull([OffPeakDay]),([export]-[import])))) AS OnPeak, Sum((IIf([OffPeakHour]="OffPeakHour" Or [OffPeakDay]="OffPeakDay",([export]-[import])))) AS OffPeak FROM tblMaster GROUP BY tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.Hour_Ending HAVING (((tblMaster.OperatingDay)=Forms!frm03HourlyNAITotals!Combo13) And ((tblMaster.[TO BA_REPORT])=Forms!frm03HourlyNAITotals!Combo3));

As you can see, all I did was add one additional level of selection to my query.

 

[pr2-eugin]

What was there and what did you add next? Can you highlight them?

 

[WLC]

Original Post:
I have the following query:

SELECT DISTINCT tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.[From BA_REPORT], tblMaster.Hour_Ending, Sum(tblMaster.Import) AS SumOfImport, Sum(tblMaster.Export) AS SumOfExport, Sum(([Export]-[Import])) AS Net, Sum((IIf(IsNull([OffPeakHour]) And IsNull([OffPeakDay]),([export]-[import])))) AS OnPeak, Sum((IIf([OffPeakHour]="OffPeakHour" Or [OffPeakDay]="OffPeakDay",([export]-[import])))) AS OffPeak
FROM tblMaster
GROUP BY tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.[From BA_REPORT], tblMaster.Hour_Ending
HAVING (((tblMaster.OperatingDay)=[Forms]![frm03HourlyNAITotalsEIS]![Combo13]) AND ((tblMaster.[TO BA_REPORT])=[Forms]![frm03HourlyNAITotalsEIS]![Combo3]) AND ((tblMaster.[From BA_REPORT])=[Forms]![frm03HourlyNAITotalsEIS]![Combo49]));

When I run it as a query it runs fine (I get the intended results). But when I copy this same SQL into the RecordSource of a form (the same form mentioned above frm03HourlyNAITotalsEIS) I get no results).

Any idea why?

plog posted: Your query is based what's in the form but your form is based on what's in the query but your query is based what's in the form but your form is based on what's in the query, but your...etc.

The chicken can't come before the egg, nor vice versa.

I added: I've used this technique successfully before. Just can't quite figure out what's wrong with this one.

Here is one that works (populates my form):

SELECT DISTINCT tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.Hour_Ending, Sum(tblMaster.Import) AS SumOfImport, Sum(tblMaster.Export) AS SumOfExport, Sum(([Export]-[Import])) AS Net, Sum((IIf(IsNull([OffPeakHour]) And IsNull([OffPeakDay]),([export]-[import])))) AS OnPeak, Sum((IIf([OffPeakHour]="OffPeakHour" Or [OffPeakDay]="OffPeakDay",([export]-[import])))) AS OffPeak FROM tblMaster GROUP BY tblMaster.OperatingDay, tblMaster.[TO BA_REPORT], tblMaster.Hour_Ending HAVING (((tblMaster.OperatingDay)=Forms!frm03HourlyNAITot als!Combo13) And ((tblMaster.[TO BA_REPORT])=Forms!frm03HourlyNAITotals!Combo3));

As you can see, all I did was add one additional level of selection to my query.

That was on Feb 3rd and I haven't received a response since. Thank you for taking the time to look at this as well!