Missing ; at end of stmt?

B

Bonnie A

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.
 
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.
Click to expand...
 

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

Similar Threads

Missing data in report 5
Changing the parameters of a form 16
opening report by form! 0
Characters found at end of sql error-Repost 2
Union query - sort of 4
Query with parameter taken from a user-updated form control. 2
union query with form critera 4
Use query criteria for calculated dates 2

Top