Date based search on two query date fields + Syntax Error / Too Complicated Error

[CEROBalancing]

Hi All - I am a homegrown Access programmer so I apologize ahead of time for a potential obvious error question. I have a query [payroll] that is searching two queries for records based on a designated time frame and it should group them by the clinican ID. If I take off the date search, the query runs just fine. If I apply a date filter I receive the Expression typed incorrectly or is too complicated error.



I would like the user to enter the date beginning/end only one time versus two times due to searching ptolog.date and searching summonthlylog.date

Any assistance you could provide would be great. The sql coding is below. Thank you ahead of time!



SELECT Clinician.ID
, Clinician.[Clinician Name]
, Clinician.Location
, Clinician.ExpectedNorm
, Clinician.FTE
, SumMonthlyLog.Earnedpoints
, Sum([pto log query].[PtoPoint]+[SumMonthlyLog].[EarnedPoints]) AS TotPoints
, Sum(([expectednorm]*[workdays])*[clinician].[fte]) AS AdjExp
, ([TotPoints]/[AdjExp]) AS performance
, [summonthlylog].[date] Or [pto log query].[date] AS DateSearch


FROM (Clinician INNER JOIN SumMonthlyLog ON Clinician.ID = SumMonthlyLog.[Provider ID]) INNER JOIN [PTO LOG Query] ON Clinician.ID = [PTO LOG Query].[Provider ID]


GROUP BY Clinician.ID
, Clinician.[Clinician Name]
, Clinician.Location
, Clinician.ExpectedNorm
, Clinician.FTE
, SumMonthlyLog.Earnedpoints
, [pto log query].[date] Or [summonthlylog].[date]
, [summonthlylog].[date] Or [pto log query].[date]


HAVING ((([summonthlylog].[date] Or [pto log query].[date]) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date]));

[ranman256]

I thnk it's you dates.
, [pto log query].[date] Or [summonthlylog].[date]
, [summonthlylog].[date] Or [pto log query].[date]


Did. You build this in query builder?
it should be...
(tbl1.date between forms!frm!date and forms...date) or (tbl2.date between forms!frm!date and forms...date)

[CEROBalancing]

built with a combination. The field addition component was done on the SQL side. I pulled the date thread from a previous code.

[CEROBalancing]

Thank you for your response ranman256


I made the adjustment and I am no longer receiving the error, but it is returning records that are not in the date range. Any thoughts on how that could be happening? I am running the search from 3/1/15 - 3/30/15 and my record for 4/7/15 is showing up

GROUP BY
Clinician.ID,
Clinician.[Clinician Name],
Clinician.Location,
Clinician.ExpectedNorm,
Clinician.FTE,
SumMonthlyLog.Earnedpoints,
[PTO LOG Query].Date,
SumMonthlyLog.Date


HAVING ((([PTO LOG Query].Date) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date])) OR (((SumMonthlyLog.Date) Between [forms]![Report Date Range]![Beginning Date] And [forms]![Report Date Range]![Ending Date]));

[ranman256]

Are you sure? That OR condition could encompass a lot of dates.