Results 1 to 6 of 6

Using Variable In VBA

  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Using Variable In VBA

    Hi - I am attempting to use a VBA variable in my sql string to build a query. This is my syntax, but I think I have messed up where the single apostrophe goes to surround the string value that I am using for my variable. Can someone analyze and let me know here?



    Code:
    Dim str As String
    
    
    str = "Select [D T].[F Name], [D T].[S N].Value, [A S].[C N] " & _ 
          "FROM [A S] Inner Join [D T] ON ([A S].ID = [D T].[S N].Value) " & _
          "AND ([A S].ID = [D T].[C N].Value) " & _
          "AND [D T].[A N] '" & strValue & "'" & _
          "Where (((D T].[A T]) = 99322) AND (([D T].[A S]) = 'Ready')) " & _
          "Order By [D T].[A N];"

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,537
    instead , you should use a query.
    the 'variable' would be what is in the form: listbox,combo,etc

    select * from table where [state]=forms!myform!txtBox

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    244
    you should do a debug.print to see what your sql resolves to.

    my guess is eliminate the & "'" and add the ' before the where.


    str = "Select [D T].[F Name], [D T].[S N].Value, [A S].[C N] " & _
    "FROM [A S] Inner Join [D T] ON ([A S].ID = [D T].[S N].Value) " & _
    "AND ([A S].ID = [D T].[C N].Value) " & _
    "AND [D T].[A N] '" & strValue & _
    "' Where (((D T].[A T]) = 99322) AND (([D T].[A S]) = 'Ready')) " & _
    "Order By [D T].[A N];"

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    434
    Code:
    Dim str As String
    
    
    str = "Select [D T].[F Name], [D T].[S N].Value, [A S].[C N] " & _ 
          "FROM [A S] Inner Join [D T] ON ([A S].ID = [D T].[S N].Value) " & _
          "AND ([A S].ID = [D T].[C N].Value) " & _
          "AND [D T].[A N] '" & strValue & "'" & _
          "Where (((D T].[A T]) = 99322) AND (([D T].[A S]) = 'Ready')) " & _
          "Order By [D T].[A N];"
    You have no equal sign between [D T].[A N] 'strValue'.
    Code:
    "AND [D T].[A N] = '" & strValue & "'" & _

  5. #5
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    I have added in a Debug.Print and have set to view my Immediate, Locals and Watches - but I do not see anything being printed.

    Is there an additional window I need to enable in the VBA Editor?

  6. #6
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Nevermind, I realize now I have to have the immediate window open before I print or nothing will show.

    That was a HUGE help!!

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

Similar Threads

  1. Replies: 3
    Last Post: 05-28-2013, 11:53 AM
  2. Replies: 2
    Last Post: 03-15-2013, 11:49 AM
  3. Replies: 0
    Last Post: 08-10-2011, 10:59 AM
  4. Replies: 4
    Last Post: 08-05-2010, 12:26 PM
  5. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 12: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
  •  
Tech Forums: Microsoft Office Forums