Missing ; at end of stmt?


G

Guest

Hello everyone! Using A02 on XP. I am trying to combine 2 select queries
into an SQL so I can have one set of data. I am getting the error saying I
am 'missing semicolon (;) at end of SQL statement'.

Here is my SQL:

PARAMETERS [Forms]![fCriteriaFrom/To]![BeginDate] DateTime,
[Forms]![fCriteriaFrom/To]![EndDate] DateTime;
SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date Recd],[Add'l
Info Req]) AS Part1Days, WorkingDays2([Add'l Info Recd],[Date Completed]) AS
Part2Days, Proposals.[Agt/Agcy Name], Proposals.[# of Proposals],
Proposals.[Date Recd], Proposals.[Add'l Info Req], Proposals.[Add'l Info
Recd], Proposals.[Date Completed], Proposals.[Date Sold], Proposals.[Grp Pen
Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Not Null) AND ((Proposals.[Add'l Info Recd]) Is Not Null) AND
((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION

PARAMETERS [Forms]![fCriteriaFrom/To]![EndDate] DateTime,
[Forms]![fCriteriaFrom/To]![BeginDate] DateTime;
UNION ALL SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date
Recd],[Date Completed]) AS Part1Days, 0 AS Part2Days, Proposals.[Agt/Agcy
Name], Proposals.[# of Proposals], Proposals.[Date Recd], Proposals.[Add'l
Info Req], Proposals.[Add'l Info Recd], Proposals.[Date Completed],
Proposals.[Date Sold], Proposals.[Grp Pen Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Null) AND ((Proposals.[Date Completed]) Is Not Null)) OR (((Proposals.[Date
Recd]) Between [Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Recd]) Is
Null) AND ((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION;

I created it from 2 regular select queries that I switched to SQL and then
pasted together into a new query in SQL view. I added the UNION ALL (tried
with and w/o the "ALL").

I'm sure that, once again, I'm missing some simple thing. (I hope!) Any
help or advice would be appreciated! Thanks in advance for your time.
 
Ad

Advertisements

J

John Spencer

Try removing the second Parameters clause

If that fails then I would also remove the first "WITH OWNERACCESS OPTION"

PARAMETERS [Forms]![fCriteriaFrom/To]![BeginDate] DateTime,
[Forms]![fCriteriaFrom/To]![EndDate] DateTime;
SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date Recd],[Add'l
Info Req]) AS Part1Days, WorkingDays2([Add'l Info Recd],[Date Completed]) AS
Part2Days, Proposals.[Agt/Agcy Name], Proposals.[# of Proposals],
Proposals.[Date Recd], Proposals.[Add'l Info Req], Proposals.[Add'l Info
Recd], Proposals.[Date Completed], Proposals.[Date Sold], Proposals.[Grp Pen
Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Not Null) AND ((Proposals.[Add'l Info Recd]) Is Not Null) AND
((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION

UNION ALL
SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date
Recd],[Date Completed]) AS Part1Days, 0 AS Part2Days, Proposals.[Agt/Agcy
Name], Proposals.[# of Proposals], Proposals.[Date Recd], Proposals.[Add'l
Info Req], Proposals.[Add'l Info Recd], Proposals.[Date Completed],
Proposals.[Date Sold], Proposals.[Grp Pen Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Null) AND ((Proposals.[Date Completed]) Is Not Null)) OR (((Proposals.[Date
Recd]) Between [Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Recd]) Is
Null) AND ((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Bonnie A said:
Hello everyone! Using A02 on XP. I am trying to combine 2 select queries
into an SQL so I can have one set of data. I am getting the error saying
I
am 'missing semicolon (;) at end of SQL statement'.

Here is my SQL:

PARAMETERS [Forms]![fCriteriaFrom/To]![BeginDate] DateTime,
[Forms]![fCriteriaFrom/To]![EndDate] DateTime;
SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date
Recd],[Add'l
Info Req]) AS Part1Days, WorkingDays2([Add'l Info Recd],[Date Completed])
AS
Part2Days, Proposals.[Agt/Agcy Name], Proposals.[# of Proposals],
Proposals.[Date Recd], Proposals.[Add'l Info Req], Proposals.[Add'l Info
Recd], Proposals.[Date Completed], Proposals.[Date Sold], Proposals.[Grp
Pen
Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Not Null) AND ((Proposals.[Add'l Info Recd]) Is Not Null) AND
((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION

PARAMETERS [Forms]![fCriteriaFrom/To]![EndDate] DateTime,
[Forms]![fCriteriaFrom/To]![BeginDate] DateTime;
UNION ALL SELECT [Part1Days]+[Part2Days] AS TotalDays, WorkingDays2([Date
Recd],[Date Completed]) AS Part1Days, 0 AS Part2Days, Proposals.[Agt/Agcy
Name], Proposals.[# of Proposals], Proposals.[Date Recd], Proposals.[Add'l
Info Req], Proposals.[Add'l Info Recd], Proposals.[Date Completed],
Proposals.[Date Sold], Proposals.[Grp Pen Office No]
FROM Proposals
WHERE (((Proposals.[Date Recd]) Between
[Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Req]) Is
Null) AND ((Proposals.[Date Completed]) Is Not Null)) OR
(((Proposals.[Date
Recd]) Between [Forms]![fCriteriaFrom/To]![BeginDate] And
[Forms]![fCriteriaFrom/To]![EndDate]) AND ((Proposals.[Add'l Info Recd])
Is
Null) AND ((Proposals.[Date Completed]) Is Not Null))
WITH OWNERACCESS OPTION;

I created it from 2 regular select queries that I switched to SQL and then
pasted together into a new query in SQL view. I added the UNION ALL
(tried
with and w/o the "ALL").

I'm sure that, once again, I'm missing some simple thing. (I hope!) Any
help or advice would be appreciated! Thanks in advance for your time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top