Please help me what is my syntax error

[TheSpecialist]

SELECT T1.YourNumber, T1.YourDate,
MoonPhaseTable.YourNumber AS MoonPhaseNumber
FROM YourTable AS T1 INNER JOIN MoonPhaseTable
ON T1.YourDate = MoonPhaseTable.YourDate
WHERE T1.YourDate IN
SELECT YourDate
FROM YourTable AS T2
WHERE T2.YourNumber = [Enter Number]
UNION ALL
SELECT YourDate-1
FROM YourTable AS T3
WHERE T3.YourNumber = [Enter Number]
UNION ALL
SELECT YourDate+1
FROM YourTable AS T4
WHERE T4.YourNumber = [Enter Number]
ORDER BY T1.YourDate;

 

[plog]

Technically the syntax error is the ORDER BY clause. But you have other issues. Every SELECT in a UNION needs to have the same number of fields. The first SELECT has 3, every other has 1. You either need to add 2 more fields to the SELECT clauses of the ones with 1, or remove 2 from the one with 3.


As for the ORDER BY. Think of the UNION ALL as an addition operation. You are adding data from 4 individual queries together. That ORDER BY clause is only being applied to the last of those queries. Since its built on T4 and you are trying to ORDER BY a field in T1, it has no idea what T1 is. Remove the ORDER BY to remove the syntax error.

 

[TheSpecialist]

This is the error I keep getting

In operator without() in query expression 'T1. YourDate IN
SELECT YourDate
FROM YourTable AS T2
WHERE T2. YourNumber=[Enter Number]

 

[plog]

Ahh, I missed the IN.

IN needs to be followed by parenthesis. You shoudl place an open one between IN and SELECT and then a closing one after the last [Enter Number].

 

[pbaldy]

Welcome to the site! I removed your duplicate thread. Please don't post the same question twice.

 

[TheSpecialist]

Hey I still getting an error

 

[pbaldy]

I wonder what the SQL is now, and what the error might be.

 

[Galaxiom]

As for the ORDER BY. Think of the UNION ALL as an addition operation. You are adding data from 4 individual queries together. That ORDER BY clause is only being applied to the last of those queries.

No so.

ORDER BY is applied after UNION.

 

[TheSpecialist]

Help me add another table to this

This is what I have on my sql now. I want to to add my MoonPhaseTable to this how do I do it what would be my sql

PARAMETERS [Enter Number:] Text ( 255 );
SELECT T1.YourNumber AS [Operative Number], T1.YourDate AS [Operative Date], T2.YourNumber AS [Related Number], T2.YourDate AS [Related Date]
FROM YourTable AS T1, YourTable AS T2
WHERE (((T1.YourNumber)=[Enter Number:]) AND ((T2.YourDate)>=[T1].[YourDate]-2 And (T2.YourDate)<=[T1].[YourDate]+2 And (T2.YourDate)<>[T1].[YourDate]))
ORDER BY T1.YourDate, T2.YourDate;

 

[jdraw]

Specialist,

You are not doing yourself any favors.
You will have many fewer syntax errors if you adopt a naming convention that does not allow embedded spaces in field or object names.

Enter Number as a text datatype???

For SQL, I recommend you spend some time at w3schools SQL You can "play SQL" with some of the tutorials to practice.

Review UNION and UNION ALL.

Also, asking readers to find syntax errors without some business context often leads to many posts. Readers don't know WHAT you are trying to do, and can only see HOW you have attempted to do it, and that isn't working. We really need to know what it is before offering more focused advice.