The query expression is too compliated bla bla bla

[Gina Maylone]

Happy Friday all! I am trying to get a report to display records either between the start and end date entered on the main form, or show all dates. Have done this a thousand times before but now Access wants to get funky (or my brain does). Here's my sql:

SELECT Members.MemberID_PK, Accounts.DBAmount, Accounts.DBDate, Accounts.Details, Accounts.CRAmount, Accounts.CRDATE, Accounts.Comments, Accounts.CheckNumber, Accounts.AsmtType
FROM Members INNER JOIN Accounts ON Members.MemberID_PK = Accounts.MemberID_FK
WHERE (((Accounts.DBDate) Between [FORMS]![MEMBERS]![SDATE] And [forms]![members]![edate]) AND ((Accounts.AsmtType) Like "*" & "monthly" & "*")) OR (((Accounts.CRDATE) Between [FORMS]![MEMBERS]![SDATE] And [forms]![members]![edate]) AND ((Accounts.AsmtType) Like "*" & "monthly" & "*"));

have also tried:

SELECT Members.MemberID_PK, Accounts.DBAmount, Accounts.DBDate, Accounts.Details, Accounts.CRAmount, Accounts.CRDATE, Accounts.Comments, Accounts.CheckNumber, Accounts.AsmtType
FROM Members INNER JOIN Accounts ON Members.MemberID_PK = Accounts.MemberID_FK
WHERE (((Accounts.DBDate) Between Nz([FORMS]![MEMBERS]![SDATE],#1/1/1900#) And Nz([FORMS]![MEMBERS]![End Date],#1/1/2199#)) AND ((Accounts.AsmtType) Like "*" & "monthly" & "*")) OR (((Accounts.CRDATE) Between Nz([FORMS]![MEMBERS]![SDATE],#1/1/1900#) And Nz([FORMS]![MEMBERS]![End Date],#1/1/2199#)) AND ((Accounts.AsmtType) Like "*" & "monthly" & "*"));

With or without a date entered I get no results. Running the query by itself gives me the "too complicated" error.

TIA! ~PEACE~

[Bob Fitz]

What results do you get without any WHERE clause

[isladogs]

Not sure if you re using a query or a SQL statement.
Try the following

SELECT Members.MemberID_PK, Accounts.DBAmount, Accounts.DBDate, Accounts.Details, Accounts.CRAmount, Accounts.CRDATE, Accounts.Comments, Accounts.CheckNumber, Accounts.AsmtType
FROM Members INNER JOIN Accounts ON Members.MemberID_PK = Accounts.MemberID_FK
WHERE (((Accounts.DBDate) Between #" & [FORMS]![MEMBERS]![SDATE] & "# And #" & [forms]![members]![edate]) & "# AND ((Accounts.AsmtType) Like '*monthly*')) OR (((Accounts.CRDATE) Between #" & [FORMS]![MEMBERS]![SDATE] & "# And "# & [forms]![members]![edate] & "#) AND ((Accounts.AsmtType) Like '*monthly*'));

[Gina Maylone]

Hi Bob!

No criteria whatsoever, I get all of the account records.

THANKS!

[Gina Maylone]

Hi Ridders52!

Getting a syntax error in date

Thanks!

[isladogs]

Sorry I was typing on a tablet and made a mistake. Corrected version below with change in RED - hopefully correct this time

SELECT Members.MemberID_PK, Accounts.DBAmount, Accounts.DBDate, Accounts.Details, Accounts.CRAmount, Accounts.CRDATE, Accounts.Comments, Accounts.CheckNumber, Accounts.AsmtTypeFROM Members INNER JOIN Accounts ON Members.MemberID_PK = Accounts.MemberID_FK
WHERE (((Accounts.DBDate) Between #" & [FORMS]![MEMBERS]![SDATE] & "# And #" & [forms]![members]![edate]) & "# AND ((Accounts.AsmtType) Like '*monthly*')) OR (((Accounts.CRDATE) Between #" & [FORMS]![MEMBERS]![SDATE] & "# And #" & [forms]![members]![edate] & "#) AND ((Accounts.AsmtType) Like '*monthly*'));