group and having

[vicki]

the orginal query:



SELECT DelMonth, DelDay, DelYear, Count(DelDate) AS countOfDelDate
FROM mytbl
WHERE item LIKE '70%-113%' OR item LIKE '73%-113%'
GROUP BY DelMonth, DelDay, DelYear
HAVING DelDay=21 AND DelMonth=02 AND DelYear=2014

The original query i have above is actual select each fields from the table. I need to re-write this by select M, D, Y from the deliverydate but got an error. What did i have wrong here, can you plz help?
thanks

Modified:

Select
day(delDate) as del_d,
month(delDate) as del_m,
year(delDate) as del_y
FROM mytbl
WHERE item LIKE '70%-113%' OR item LIKE '73%-113%'
group by del_d,del_m,del_y
having del_d = 21
and del_m =02
and del_y = 2014

[June7]

Don't use # delimiters because these are not date values.

Shouldn't you be using * wildcard?

[vicki]

no it's just the variables passed in from the form. I have it corrected, please let me know why i still got an error from the query.
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'day(delDate)' as part of an aggregate function.

[June7]

Are you building this query in Access query builder? Or is this a VBA construct?

[orange]

It appears that

day(delDate) as del_d

has not been recognized as del_d in your Group By.

Please tell us about your use of Access. I see java jdbc, odbc and Macromedia.