Why am I being asked to enter a Parameter Value...?

[djclntn]

SELECT [Employees Payroll Query].epEmployeeID AS EmployeeID, Max([Employees Payroll Query].epDate) AS LastDateWorked, [LastDateWorked]-Date() AS DaysSinceWorked
FROM [Employees Payroll Query]


GROUP BY [Employees Payroll Query].epEmployeeID, [LastDateWorked]-Date()
HAVING ((([Employees Payroll Query].epEmployeeID) Is Not Null))
ORDER BY Max([Employees Payroll Query].epDate) DESC;


Enter a Parameter Value for LastDateWorked

[pbaldy]

Because the GROUP BY clause is evaluated before the SELECT clause. You'll need to repeat the Max there.

[djclntn]

I don't quite follow. I tried putting Max after GROUPP BY, but that didn't work. Might youb be kind enough to send back the correct SQL?

[pbaldy]

See if this works:

SELECT [Employees Payroll Query].epEmployeeID AS EmployeeID, Max([Employees Payroll Query].epDate) AS LastDateWorked, [LastDateWorked]-Date() AS DaysSinceWorked
FROM [Employees Payroll Query]
GROUP BY [Employees Payroll Query].epEmployeeID, Max([Employees Payroll Query].epDate) -Date()
HAVING ((([Employees Payroll Query].epEmployeeID) Is Not Null))
ORDER BY Max([Employees Payroll Query].epDate) DESC;

Might need the same thing in the SELECT clause on the other field.

[djclntn]

It's telling me:
Cannot have aggregate function in GROUPBY clause Max([Employees Payroll Query].epDate) -Date()

[pbaldy]

Testing would indicate I made a mistake there.

I could swear I've done that, but perhaps not. What happens if you drop that bit from the GROUP BY clause completely? In design view, that would mean changing Group By to Expression. If that doesn't work, you may need to create one query that just groups on employee and returns the max date, and then base a second query on that one.

[djclntn]

It Worked. Thanks!

[pbaldy]

No problem; sorry about leading you down the wrong path.

[djclntn]

pd: Can I ask you A ? regarding spcl chrctrs. In a query you can remame any fld name by putting a : after the chosen name - eg. X\X: Does this mean that I can then use spcl chrctrs then, or is it still wise not to do so?

I hope you unstand my shorthand

[pbaldy]

You can often get away with it, but I never do. My thinking is, why risk it? You can put anything in a form/report label for user clarity. From this link:

http://support.microsoft.com/default...b;EN-US;209187

The following symbols must not be used as part of a field name or as part of an object name:

. / * : ! # & - ? ; " ' $

[djclntn]

Paul, sorry if I'm bugging you here. 2-quick ?'s

1. \ is not a spcl chrctr that's weird...? Funny thing is I used that particular character a lot, but have been stopping & going back & changing my fld names.

2. I believe to be right from experience, but I want to make sure. Say in a query I have a field name, psW which I changed to be, W: [psW] If I need to refer to this field in a cacl/expression I can just refer to the W, or ought I be referring to, psW?

Thanks a lot for your time!
Dave...

[pbaldy]

I wouldn't use the \ either, as if you tried to use the field in a file path you'd be hosed. Like I said, you can often get away with it, but why risk it?

Within the query, it depends. You'll sometimes be able to refer to the alias in other field calculations in the SELECT clause, but not in other clauses, as you discovered above.