Too complex expression

[Guest]

I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

Thanks:

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

 

[Marshall Barton]

I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

You have used incorrect syntax in the reference to the form
controls. It should be an ! instead of a .
forms!frmGetCalendar.txtDate

As best I can tell, because there are no aggregate
functions, you have no need to use a GROUP BY clause. I
think you should remove the entire GROUP BY clause and
change the HAVING clause to a WHERE clause.

That WHERE clause looks overly complicated. Try shortening
it to:

WHERE (SchedDate = Forms!frmGetCalendar.txtDate)
AND (tblEmployee.TermDate Is Null)

 

[Ken Snell [MVP]]

The correct syntax for referencing a control on a form is this:

[forms]![frmGetCalendar]![txtDate]

Note the use of the bang operator ( ! ), not the dot operator ( . ).

 

[Guest]

Thanks for your help. I took out the GROUP BY clause, and changed the HAVING
clause to your WHERE clause. However, now the report shows multiple rows of
the same employee with the same data in each. Whereas before it showed only
the one row for the employee. If I add the group by it works for the
schedule part, but I have to subforms, and it only shows the one that has to
do with tblSchedule. If I add the other table (tblVacation) where
VacationDate > 0 it show multiples again.

I am trying to show all employees so if there is no vacation and they are
not scheduled their name will still appear on the schedule. I think I need
the grouping so the name is only displayed once. Any thoughts?

I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

You have used incorrect syntax in the reference to the form
controls. It should be an ! instead of a .
forms!frmGetCalendar.txtDate

As best I can tell, because there are no aggregate
functions, you have no need to use a GROUP BY clause. I
think you should remove the entire GROUP BY clause and
change the HAVING clause to a WHERE clause.

That WHERE clause looks overly complicated. Try shortening
it to:

WHERE (SchedDate = Forms!frmGetCalendar.txtDate)
AND (tblEmployee.TermDate Is Null)

 

[Guest]

Actually I noticed if I put the filter to SchedDate it only shows employees
that are scheduled on that date, but I need all employees that are scheduled
that week.

Thanks for your help. I took out the GROUP BY clause, and changed the HAVING
clause to your WHERE clause. However, now the report shows multiple rows of
the same employee with the same data in each. Whereas before it showed only
the one row for the employee. If I add the group by it works for the
schedule part, but I have to subforms, and it only shows the one that has to
do with tblSchedule. If I add the other table (tblVacation) where
VacationDate > 0 it show multiples again.

I am trying to show all employees so if there is no vacation and they are
not scheduled their name will still appear on the schedule. I think I need
the grouping so the name is only displayed once. Any thoughts?

I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

You have used incorrect syntax in the reference to the form
controls. It should be an ! instead of a .
forms!frmGetCalendar.txtDate

As best I can tell, because there are no aggregate
functions, you have no need to use a GROUP BY clause. I
think you should remove the entire GROUP BY clause and
change the HAVING clause to a WHERE clause.

That WHERE clause looks overly complicated. Try shortening
it to:

WHERE (SchedDate = Forms!frmGetCalendar.txtDate)
AND (tblEmployee.TermDate Is Null)

 

[Ken Snell [MVP]]

Additionally, because you're using a LEFT JOIN from tblEmployees to
tblSchedule, it is possible that the fields from tblSchedule may be Null
because there is no match to an EmployeeID value. That will make the DateAdd
function "choke" and that can lead to the error that you're seeing.

--

Ken Snell
<MS ACCESS MVP>

The correct syntax for referencing a control on a form is this:

[forms]![frmGetCalendar]![txtDate]

Note the use of the bang operator ( ! ), not the dot operator ( . ).


--

Ken Snell
<MS ACCESS MVP>

I am trying to show all employees. If I setup the query to show where
they
are equal it works. What am I doing wrong?

Thanks:

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

 

[Marshall Barton]

Yes, GROUP BY will get rid of the duplicates, but before
putting all that back, try using the DISTINCT keyword
instead:
SELECT DISTINCT DateAdd(
--
Marsh
MVP [MS Access]

Thanks for your help. I took out the GROUP BY clause, and changed the HAVING
clause to your WHERE clause. However, now the report shows multiple rows of
the same employee with the same data in each. Whereas before it showed only
the one row for the employee. If I add the group by it works for the
schedule part, but I have to subforms, and it only shows the one that has to
do with tblSchedule. If I add the other table (tblVacation) where
VacationDate > 0 it show multiples again.

I am trying to show all employees so if there is no vacation and they are
not scheduled their name will still appear on the schedule. I think I need
the grouping so the name is only displayed once. Any thoughts?

I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

You have used incorrect syntax in the reference to the form
controls. It should be an ! instead of a .
forms!frmGetCalendar.txtDate

As best I can tell, because there are no aggregate
functions, you have no need to use a GROUP BY clause. I
think you should remove the entire GROUP BY clause and
change the HAVING clause to a WHERE clause.

That WHERE clause looks overly complicated. Try shortening
it to:

WHERE (SchedDate = Forms!frmGetCalendar.txtDate)
AND (tblEmployee.TermDate Is Null)

 

[Marshall Barton]

Ahhh, that's what that DateAdd stuff was all about. Sorry,
I misinterpreted that.

Put the DateAdd stuff back in the where clause.
--
Marsh
MVP [MS Access]

Actually I noticed if I put the filter to SchedDate it only shows employees
that are scheduled on that date, but I need all employees that are scheduled
that week.

Thanks for your help. I took out the GROUP BY clause, and changed the HAVING
clause to your WHERE clause. However, now the report shows multiple rows of
the same employee with the same data in each. Whereas before it showed only
the one row for the employee. If I add the group by it works for the
schedule part, but I have to subforms, and it only shows the one that has to
do with tblSchedule. If I add the other table (tblVacation) where
VacationDate > 0 it show multiples again.

I am trying to show all employees so if there is no vacation and they are
not scheduled their name will still appear on the schedule. I think I need
the grouping so the name is only displayed once. Any thoughts?

James wrote:
I am trying to show all employees. If I setup the query to show where they
are equal it works. What am I doing wrong?

--------------------------begin sql----------------------------------
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf,
tblSchedule.EmployeeID, [LastName] & "," AS LName, tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@") AS Phone, tblEmployee.TermDate
FROM tblEmployee LEFT JOIN tblSchedule ON tblEmployee.EmployeeID =
tblSchedule.EmployeeID
GROUP BY DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1,
tblSchedule.EmployeeID, [LastName] & ",", tblEmployee.FirstName,
Format([PhoneNumber],"(@@@) @@@-@@@@"), tblEmployee.TermDate
HAVING
(((DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1)=DateAdd("d",-Weekday([forms].[frmGetCalendar].[txtDate]),[forms].[frmGetCalendar].[txtDate])+1)
AND ((tblEmployee.TermDate) Is Null));
--------------------------end sql----------------------------------

You have used incorrect syntax in the reference to the form
controls. It should be an ! instead of a .
forms!frmGetCalendar.txtDate

As best I can tell, because there are no aggregate
functions, you have no need to use a GROUP BY clause. I
think you should remove the entire GROUP BY clause and
change the HAVING clause to a WHERE clause.

That WHERE clause looks overly complicated. Try shortening
it to:

WHERE (SchedDate = Forms!frmGetCalendar.txtDate)
AND (tblEmployee.TermDate Is Null)