Error: You tried to execute a query that doesn't include .... 1

[cutestuff]

hi,

What is wrong with my query?
Please help. I'm going crazy!

I keep getting this error:
"You tried to execute a query that doesn't include the specified expression qryTO.MonthDate = [Forms]![frmM]![cbo_Month] as part of an aggregate function"

SELECT DateAdd("d",-Weekday([TDate]),[TDate])+1 AS WeekOf, [qryTO].[MonthDate] FROM [qryTO]   GROUP BY DateAdd("d",-Weekday([TDate]),[TDate])+1, [qryTO].[MonthDate] HAVING ((([qryTO].[MonthDate])=[Forms]![frmM]![cbo_Month])) ORDER BY [qryTO].[MonthDate];

Thanks,
Jen

 

[traingamer]

Try:

SELECT DateAdd("d",-Weekday([TDate]),[TDate])+1 AS WeekOf, [qryTO].[MonthDate] FROM [qryTO]   GROUP BY DateAdd("d",-Weekday([TDate]),[TDate])+1, [qryTO].[MonthDate] HAVING ((([qryTO].[MonthDate])=[Forms]![frmM]![cbo_Month][COLOR=blue].value[/color])) ORDER BY [qryTO].[MonthDate];

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[cutestuff]

hi traingamer,

That didn't work for me.

If I take out everything starting from the HAVING clause it works - but of course I need to filter the query based on the month the user selects in the combo box.

 

[lespaul]

ok FYI - Access uses the HAVING clause incorrectly. Unless you are filtering by the aggregate (like: HAVING SUM(Amount) > 2000) you should use a WHERE clause. Unless you are using an aggregate function (like SUM, COUNT, AVG, MIN, MAX) you don't need a GROUP BY clause unless you just want a single distinct record where there are multiple records that meet the criteria. Does this do what you need?

SELECT DateAdd("d",-Weekday([TDate]),
[TDate])+1 AS WeekOf, 
[qryTO].[MonthDate] 
FROM [qryTO]   
WHERE [qryTO].[MonthDate])=[Forms]![frmM]![cbo_Month]
ORDER BY [qryTO].[MonthDate];

Leslie

Have you met Hardy Heron?

 

[BigRed1212]

I want to post:

SELECT DateAdd("d",-Weekday([TDate]),[TDate])+1 AS WeekOf, [qryTO].[MonthDate] 
FROM [qryTO]
WHERE ((([qryTO].[MonthDate])=[Forms]![frmM]![cbo_Month].value)) 
GROUP BY DateAdd("d",-Weekday([TDate]),[TDate])+1, [qryTO].[MonthDate] 
ORDER BY [qryTO].[MonthDate];

but I'm not sure that will work.

 

[BigRed1212]

Ignore me. Go with Leslie.

 

[cutestuff]

OMG! That's perfect! Thanks so much Leslie! You have no idea how long I've been trying to figure out what was wrong with it!

 

[cutestuff]

hi lespaul,
sorry i have another question related to this one. I failed to do other checks before i closed the post (i was so happy it wasn't giving me the error anymore). What's happening now is that if there is more than one event occuring on a day, it duplicates that day. Say there's 2 events happening on june 18, it shows june 18 twice, if there's 3 events, it shows it 3 times.
i think i did need the group by clause but is there any way around that?
let me know if i need to post in a new thread for this.

thanks so very much.
jen

 

[cutestuff]

never mind! It's working now! Thanks for the help again!

 

[PHV]

As you don't use any aggregate function, simply use the DISTINCT predicate:
SELECT DISTINCT DateAdd(...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

Glad to help! Thanks for the star!

Leslie