Concatenation

[lantoni]

This is what is giving me the syntax error: Syntax error in query expression '[Residence in] NOT Is Null'.



SELECT ID, "Res" As StipType, [Residence in] AS Stipulation FROM [Req&Stips] WHERE [Residence in]Not Is Null


UNION SELECT ID, "Fel", [Fellowship in] FROM [Req&Stips] WHERE [Fellowship in] Not Is Null
UNION SELECT ID, "SS", [Special Skills in] FROM [Req&Stips] WHERE [Special Skills in] Not Is Null
UNION SELECT ID, "Exp", [Experience in] FROM [Req&Stips] WHERE [Experience in] Not Is Null
UNION SELECT ID, "CA", [Contractual Arrangements] FROM [Req&Stip] WHERE [Contractual Arrangements] Not Is Null
UNION SELECT ID, "OS", [Other Stipulations] FROM [Req&Stips] WHERE [Other Stipulations] Not Is Null
UNION SELECT ID, "Req", [Requirements] FROM [Req&Stips] WHERE [Requirements] Not Is Null;

[orange]

Try Is NOT NULL rather than Not IS NULL. I don't see anything else, and am not sure why Not Is NULL.
Try it and see. Post back if issues.
There is a space after ] and before Not Is NULL, right?

[lantoni]

OK, I used IS Not and it worked, finally :-) .....see below:


SELECT ID, "Res" As StipType, [Residence in] AS Stipulation FROM [Requirements and Stipulations Tbl] WHERE [Residence in] Is Not Null
UNION SELECT ID, "Fel", [Fellowship in] FROM [Requirements and Stipulations Tbl] WHERE [Fellowship in] Is Not Null
UNION SELECT ID, "SS", [Special Skills in] FROM [Requirements and Stipulations Tbl] WHERE [Special Skills in] Is Not Null
UNION SELECT ID, "Exp", [Experience in] FROM [Requirements and Stipulations Tbl] WHERE [Experience in] Is Not Null
UNION SELECT ID, "CA", [Contractual Arrangements] FROM [Requirements and Stipulations Tbl] WHERE [Contractual Arrangements] Is Not Null
UNION SELECT ID, "OS", [Other Stipulations] FROM [Requirements and Stipulations Tbl] WHERE [Other Stipulations] Is Not Null
UNION SELECT ID, "Req", [Requirements] FROM [Requirements and Stipulations Tbl] WHERE [Requirements] Is Not Null;


My question now is what do I need to do to show the unique ID field [Slot Number] on the query?

[orange]

Any field you want to display has to be included in the SELECT.
Where is [SLOT NUMBER] located?

[lantoni]

It is on the same table [Requirements and Stipulations Tbl]. It's the unique ID . Once the I run the query I need to see the [SLOT NUMBER] field and the result of the UNION query... does that make sense to you?

[orange]

No.
How do you determine the ID (slot number) you want? Is there some criteria?
Do you not see the ID in the query you have?

[lantoni]

The [Slot Number] is created when we enter new data in the database. It follows the following rule: S14-0001 would mean, it is a slot requested in Spring of 2014,and it was the first one requested. Other variations are: F for fall requests and E for extra (out of cycle) requests.

[lantoni]

right now when I run the sequel, my [Slot number] field is not showing, it shows a generic ID number, most likely an autonumber.

[June7]

Include whatever fields you want in the same manner as already shown. If your unique identifier is [Slot Number] instead of ID, then use [Slot Number].

[lantoni]

June7,
I did that, I used [Slot Number] instead of ID but it returns only digits. My [Slot Number] field contains letters and numbers like I posted above, S14-0001.

[orange]

Please post the sql code you used.

[lantoni]

Here is what I used:

SELECT [Slot Number] , "Res" As StipType, [Residence in] AS Stipulation FROM [Requirements and Stipulations Tbl] WHERE [Residence in] Is Not Null
UNION SELECT ID, "Fel", [Fellowship in] FROM [Requirements and Stipulations Tbl] WHERE [Fellowship in] Is Not Null
UNION SELECT ID, "SS", [Special Skills in] FROM [Requirements and Stipulations Tbl] WHERE [Special Skills in] Is Not Null
UNION SELECT ID, "Exp", [Experience in] FROM [Requirements and Stipulations Tbl] WHERE [Experience in] Is Not Null
UNION SELECT ID, "CA", [Contractual Arrangements] FROM [Requirements and Stipulations Tbl] WHERE [Contractual Arrangements] Is Not Null
UNION SELECT ID, "OS", [Other Stipulations] FROM [Requirements and Stipulations Tbl] WHERE [Other Stipulations] Is Not Null
UNION SELECT ID, "Req", [Requirements] FROM [Requirements and Stipulations Tbl] WHERE [Requirements] Is Not Null;

[orange]

You have to use [Slot Number] for all the IDs. So each UNION....ID has to be UNION...[Slot Number]..

[lantoni]

Just so I understand, what is the use of "Res", "Fel", "SS", used for? - just trying to learn - thanks for the patience.


Oh! By looking at waht I post it kind of jumped to mey face,I probably should have suybstituted ID for [Slot Number] in every single line rigth? I;m going to try

[lantoni]

Done!!! Thank you all so much!