Syntax Error in Query (1 Viewer)

BettyWho · Jan 18, 2018

I am having an issue getting the query below to run successfully. I have a similar modified query and it runs fine this one however is returning a syntax error and I can't find the error, and it could be a case of staring at it for too long so I've missed it.

What I am trying to do is isolate new clients added in a particular period where there is no closed box ticked and the open dates is in a certain period. as well as gathering how they became clients. Any advice and assistance you can offer would be appreciated. Thanks in advance.

PARAMETERS [StartDate] DateTime, [End Date] DateTime;
SELECT count(ClientID) AS ClientCount, ReferralType
FROM (SELECT DISTINCT ClientID, ReferralAgent AS ReferralType FROM (SELECT ClientID, ReferralAgent, ClosureDate, CurrentOpenDate, DateFirstOpened FROM TClientInfo WHERE ((((Closuredate) Is Null) AND ((CurrentOpenDate)>= [Startdate] AND <=[End Date])) Or (((Closuredate) Is Null) AND ((DatefirstOpened)<=[End Date])) Or (StartDate <= [ClosureDate] and [ClosureDate] <= [End Date]) OR ((Closuredate)>=[End Date]))) AS C) AS [%$##@_Alias]
GROUP BY ReferralType;

plog · Jan 18, 2018

Perhaps others will parse through it, I'm only willing to give you general advice:

~ Divide and conquer-- You have 3 SELECTS, start with the inner-most and see if it works by itself. If it does, keep moving up a level until you find the offender.

~ Count the left and right parenthesis and make sure they equal each other.

~ Make it human readable. Paste it into a text editor and add line breaks and spaces so a human (you) can actually work through it and be able to more easily spot issues.

BettyWho · Jan 21, 2018

I've done exactly this prior to posting here and all the parenthesis appear correct I even went so far as to print it and highlight them as they were correct. I wasn't sure if there was something else potentially causing a syntax error which is why I was asking for assistance. Are you able to suggest anything else I could look at? Thanks in advance.

sonic8 · Jan 21, 2018

BettyWho said:
I've done exactly this prior to posting here and all the parenthesis appear correct I even went so far as to print it and highlight them as they were correct.

Pasting the code into a text editor (e.g. Notepad++) that highlights the corresponding opening or closing bracket when you select either one is even more helpful, I think.
But in this case there is no issue with the brackets...

...WHERE ((((Closuredate) Is Null) AND ((CurrentOpenDate)>= [Startdate] AND <=[End Date])) Or (((Closuredate) Is Null) AND ((DatefirstOpened)<=[End Date])) Or (StartDate <= [ClosureDate] and [ClosureDate] <= [End Date]) OR ((Closuredate)>=[End Date]))) AS C) ...

JHB · Jan 21, 2018

BettyWho said:
..Thanks in advance.

Post a stripped down version of your database with some sample data, zip it, + description how to reproduce your problem.

arnelgp · Jan 21, 2018

Miss BettyWho?

Can you post simply the conditions of your
query one line at a time. i see some:

1. ClosureDate is null, CurrentOpenDate >= StartDate
2. ClosureDate >= StartDate, ClosureDate <= End Date.

i was thinking that you don't need SubQuery there because
we are only working on Same Table. Just different
set of conditions to apply.

Galaxiom · Jan 21, 2018

sonic8 has the highlighted the problem.

Use Between rather than less than and greater than. It is more efficient to write and run.

Formatting is everything when using subqueries and nested AND/OR. If it can't be read straight out then it is always going to be prone to error. What you have posted is unreadable.

Unfortunately Access is complete crap at displaying subqueries, even worse than what it does to ordinary queries. It also adds copious parentheses that just clutter and obscure what actually matters. In fact the whole Access SQL editor is complete rubbish.

Here is your query formatted readably using Notepad++. I have not considered the logic.

Code:

PARAMETERS [StartDate] DateTime, [End Date] DateTime;

SELECT count(ClientID) AS ClientCount, ReferralType
FROM 
	(
		SELECT DISTINCT ClientID, ReferralAgent AS ReferralType 
		FROM 
			(
				SELECT ClientID, ReferralAgent, ClosureDate, CurrentOpenDate, DateFirstOpened 
				FROM TClientInfo 
				WHERE 
                                        (
                                          Closuredate Is Null 
						AND CurrentOpenDate BETWEEN [Startdate] AND [End Date]
                                        )

					OR
 
					(
					 Closuredate Is Null
						AND DatefirstOpened <= [End Date]
					)
						
					OR StartDate BETWEEN [ClosureDate] AND [End Date]
					
					OR Closuredate)>=[End Date]
			) AS C
	) AS [%$##@_Alias]
	
GROUP BY ReferralType

UNION ALL Null, Null
FROM Dummy
;

UNION ALL is a trick to force Access to leave the formatting alone. Usually it doesn't make any difference to query performance. When it does I keep that query for editing and paste another just for running without the UNION and let Access scramble it.

Generally though, I move the resources for complex queries into SQL Server where there is a competent SQL Editor in MSSQL Studio. Once you have experienced this, Access SQL is just too painful to contemplate most of the time.

Pat Hartman · Jan 22, 2018

Why would you ever use special characters in an alias name? Access is going to see
[%$##@_Alias]
as
[______Alias]

sonic8 · Jan 22, 2018

Pat Hartman said:
Why would you ever use special characters in an alias name? Access is going to see
[%$##@_Alias]
as
[______Alias]

This alias is auto generated. Access does this whenever you write a derived-table subquery and you do not provide an alias yourself.

Pat Hartman · Jan 22, 2018

"c" wasn't good enough for Access as a name?

sonic8 · Jan 23, 2018

Pat Hartman said:
"c" wasn't good enough for Access as a name?

C was the first subquery. The second subquery appears to having had no alias.
(OT: Is that sentence correct English grammar?)

Pat Hartman · Jan 23, 2018

Technically no but it is "mother speak" and that trumps everything

Syntax Error in Query (1 Viewer)

BettyWho

Registered User.

plog

Banishment Pending

BettyWho

Registered User.

sonic8

AWF VIP

JHB

Have been here a while

arnelgp

..forever waiting... waiting for jellybean!

Galaxiom

Super Moderator

Pat Hartman

Super Moderator

sonic8

AWF VIP

Pat Hartman

Super Moderator

sonic8

AWF VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread