Missing ; at end of stmt?

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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

Characters found at end of sql error-Repost 2
Loading recordset at runtime 4
MSACCESS 97 APPLICATION ERROR CONVERTING FROM TEXT TO MEMO 4
Want to edit a table based on a query of a query of several querie 2
Object reference not set to an instance of an object. 2
HELP! Explorer.exe crashing because of shell32.dll! 16

Back
Top