Results 1 to 5 of 5
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Using Variable In SQL For Query

    I am wanting to use a variable in my via query syntax. I just can't get the syntax right. I have this, but it puts literally ' + name + ' in the query



    How should I write this so the actual value is added to the query?

    Code:
    Dim info As String
    Dim pdf As QueryDef
    
    info = "Select itemSold From Info " & _
             "Where salesperson = ' + sp + ';"
    
    Set qdf = ....
    DoCmd.OpenQuery qdf.Name
    qdf.Close
    Set pdf = Nothing

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    info = "SELECT ItemSold from Info WHERE salesperson = '" & sp & "'"

    if salesperson is a text field

    info = "SELECT ItemSold from Info WHERE salesperson = " & sp

    if salesperson is a foreign key to a salesperson table (number field)

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I'm getting a compile error
    Code:
    "Select itemSold From Info " & _
             "Where salesperson = '" & sp & "'";"

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    not
    "Where salesperson = ' " & sp & " '";"
    but
    "Where salesperson = ' " & sp & " ';"
    not that you need the semicolon in vba sql anyway. I added spaces to make the quotes more discernible for you.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you didn't use what I said. You're adding a ;" at the end which is unnecessary.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 91 Object Variable or Block Variable Not Set
    By mindbender in forum Programming
    Replies: 5
    Last Post: 05-01-2017, 12:01 PM
  2. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  3. Replies: 3
    Last Post: 05-28-2013, 12:53 PM
  4. Replies: 0
    Last Post: 08-10-2011, 11:59 AM
  5. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 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