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]));