Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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;

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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?

  3. #18
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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?

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Any field you want to display has to be included in the SELECT.
    Where is [SLOT NUMBER] located?

  5. #20
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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?

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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?

  7. #22
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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.

  8. #23
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    right now when I run the sequel, my [Slot number] field is not showing, it shows a generic ID number, most likely an autonumber.

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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.

  11. #26
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Please post the sql code you used.

  12. #27
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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;

  13. #28
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    You have to use [Slot Number] for all the IDs. So each UNION....ID has to be UNION...[Slot Number]..

  14. #29
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    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

  15. #30
    lantoni is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    20
    Done!!! Thank you all so much!

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. concatenation issue
    By SuicidalDriver in forum Queries
    Replies: 11
    Last Post: 07-31-2013, 04:42 AM
  2. Two-color concatenation?
    By KDTom in forum Forms
    Replies: 2
    Last Post: 02-01-2013, 03:13 PM
  3. Concatenation
    By Rhubie in forum Access
    Replies: 3
    Last Post: 08-30-2012, 05:09 PM
  4. Query and Concatenation
    By Try2Live4God in forum Programming
    Replies: 2
    Last Post: 05-25-2010, 03:45 PM
  5. Concatenation Urgent Help
    By Shoaib in forum Queries
    Replies: 0
    Last Post: 04-23-2009, 11:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums