Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21

    Using AND and OR in a SQL string??

    Hi there again,

    I have a problem I am trying to solve where I connect from excel VBA to and table in Access and I need to query the table for data. The table essentially contains a bunch of employees and I need to pull out the surnames of a group who have the same aircraft rating and their roles are either Captain or Co-Pilot. The SQL string needs to look something like this:



    Code:
    strSql = "SELECT Surname FROM tblCrew WHERE [AW139]=-1 AND [Role] =" & """Captain"""" & "OR [Role] =" & """Co-Pilot""";"
    The above string obviously does not work but is my best guess as to how it should look.

    Any help would be appreciated.

    Kind regards,
    Marcus

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try
    Code:
    strSQL ="SELECT Surname FROM tblCrew WHERE [AW139]=-1 AND [Role] In('Captain','Co-Pilot')"
    Do you want to know the Role of the individual?
    If yes, then use Surname, Role
    Last edited by orange; 01-15-2020 at 09:48 AM. Reason: missing quote corrected

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    IN is a better way to go for this type of requirement, but for future reference if you are going to use and/or then you need to include brackets to define the order of calculation (a bit like using +- and */ in maths)

    Plus you are missing a space before OR (and Orange's suggestion is missing a space after IN and a single quote before Co-Pilot )



    Code:
    strSql = "SELECT Surname FROM tblCrew WHERE [AW139]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"

    this will produce a different result
    Code:
    strSql = "SELECT Surname FROM (tblCrew WHERE [AW139]=-1 AND [Role] ='Captain') OR [Role] ='Co-Pilot'"

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Ajax View Post
    (and Orange's suggestion is missing a space after IN)
    In my experience no space is necessary. I never put one in when building SQL in code (between IN and its opening parentheses).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks for the replies all. I am a novice at Access and have taken the SQL string and placed it in my excel VBA code and it looks like this:

    Code:
    Function CrewRatingDB(Rating As String) As Variant
    
    Dim NamesDB As Variant
    Dim i As Long
    
    ' ## Selects the Captains and CoPilots with the required rating
    
    'get all Surnames thast meet the criteria
    Call openConnection
    
    Select Case Rating
        Case "S92"
              strSql = "SELECT Surname FROM tblCrew WHERE [S92]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"
        Case "A139"
              strSql = "SELECT Surname FROM tblCrew WHERE [A139]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"
        Case "H175"
              strSql = "SELECT Surname FROM tblCrew WHERE [H175]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"
    End Select
    
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    CrewRatingDB = rs.Fields(0)
    
    Call closeConnection
    
    End Function
    I am trying to pass all the Surnames back into the excel function however the code quits at the "Set rs =" line. Can anyone help on this?

    Also I am able to make the query using the query wizard in the Access DB, is there any way I can see the code behind the queries I make? I have gone into the VBA editor but cannot seem to find the code among lots of other modules etc that seem to be there by default.

    Kind regards,
    Marcus

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean by 'quits' - error message, wrong result, nothing happens?

    Execute method is used for action SQL - DELETE, UPDATE, INSERT - not opening a recordset.

    You have not declared recordset object. Assuming this is an ADO connection:

    Dim rs As ADODB.Recordset
    rs.Open strSQL, cn, adOpenStatic, adLockReadOnly

    Select Case can be eliminated:

    strSql = "SELECT Surname FROM tblCrew WHERE [" & Rating & "]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"

    Function will return only one value.
    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.

  7. #7
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks June7,

    I have rs declared as public an used in many subs and functions in the Module.

    Next question though this needs to return a list of names not just one entry, can you give me some pointers on how to do this?

    Kind regards,
    Marcus

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Options:

    1. loop through recordset and concatenate values to a single long, long string or write each individual value to cell

    2. write records to worksheet with CopyFromRecordset method, something like:
    ActiveWorksheet.Range("A1").CopyFromRecordset rs
    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.

  9. #9
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Thanks June7 I will research those two options and see how I go.

    Cheers,
    Marcus

  10. #10
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Hi there again,

    I have managed to code something that works with a simple SQL string that just returns all the surnames in the database however when I replace the simple string with the more complex filter it no longer works. Here is the code:

    Code:
    Function CrewRatingDB(Rating As String) As Variant
    
    Dim NamesDB As Variant
    Dim i As Long
    
    ' ## Selects the Captains and CoPilots with the required rating
    
    Call openConnection
    
    '"SELECT Count(*) FROM tblCrew;"  <--- THIS WORKS
    
    strSql = "SELECT Count(*) FROM tblCrew WHERE [" & Rating & "]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot') ORDER BY Surname;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql) '  <--- EXITS FUNCTION HERE WITH NO ERROR MSG !!!!!!!!!!!!!!!!!!!!!
    nRecords = rs.Fields(0)
    
    Call closeConnection
    
    'get all Surnames
    Call openConnection
    
    '"SELECT Surname FROM tblCrew;"  <--- THIS WORKS
    
    strSql = "SELECT Surname FROM tblCrew WHERE [" & Rating & "]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot') ORDER BY Surname;"
    
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    
    ReDim NamesDB(1 To nRecords)
    rs.MoveFirst
    i = 1
    Do While Not rs.EOF
        NamesDB(i) = rs!Surname
        i = i + 1
        rs.MoveNext
    Loop
    
    CrewRatingDB = NamesDB
    
    Call closeConnection
        
    End Function
    Leeds me to believe there is either something wrong with the syntax of the string, any ideas??

    Cheers,
    Marcus

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Well, I just learned something new. This Execute method syntax does work.

    SQL is pulling a single value with Count(*) function - there is no need for ORDER BY and since there is nothing to sort and there is no GROUP BY clause with Surname field, this is likely causing error. It does for me - but I get error message.
    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.

  12. #12
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    Just tried it without the ORDER ... and the function still ends at the same line and returns to the sub it was called from. I realised the sub it was called from had an on error goto next in it. Once I disabled it I got this error:



    I guess I am missing something in the string??

    Cheers,
    Marcus

    The image doesn't appear to show up but says:

    "No value given for one or more required parameters"

  13. #13
    Demarc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    21
    This is the sqlstr value from the watch window:

    "SELECT Count(*) FROM tblCrew WHERE [A139]=-1 AND ([Role] ='Captain' OR [Role] ='Co-Pilot')"

    Does the Captain and Co-Pilot need "" around them?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, apostrophe delimiters are just fine.

    I see nothing wrong that that SQL. I would give the Count(*) an alias name but I tested and works without one.

    If you want to provide files for analysis, follow instructions at bottom of my post.
    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.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can also try copying that SQL into a new query and running it. You may get a more descriptive error. It sounds like a bad field name or something.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2018, 03:14 PM
  2. Replies: 5
    Last Post: 02-20-2018, 07:25 PM
  3. Using A String Literal In Query String
    By Juan4412 in forum Programming
    Replies: 1
    Last Post: 05-24-2017, 08:45 AM
  4. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  5. Replies: 2
    Last Post: 04-05-2015, 06:06 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