Results 1 to 6 of 6
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    SQL queries with variables

    Can we do SQL queries with variables.
    And the variables can come from VBA tuples of another query or table field (I imagine this can't be done unless the queries in the VBA).

    For example:

    Code:
    SELECT field1
    FROM tb
    WHERE field5 = variable
    But how about the variable from the select of another query ?

    For example:

    Code:
    SELECT field1
    FROM tb
    WHERE field5 = (SELECT field4 as fname
                             FROM tb2
                             WHERE field2 = 5)
    If this can't be done, can it be done in VBA with very long and complex queries (queries in the VBA and the variables in the queries)?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What have you tried?

    It might work if "field5" is a numeric field. If text, you would need delimiters.


    If this can't be done, can it be done in VBA with very long and complex queries (queries in the VBA and the variables in the queries)?
    I use VBA to build queries "on-the-fly" all of the time.......


    Note: "Name" is a reserved word in Access and shouldn't be used as object names.

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I use VBA to build queries "on-the-fly" all of the time.......
    cool, nice. But the query is very long.
    I think it can be about 20 page for this one query.

    Imagine putting a 20 page query in a VBA. I don't think that is going to work, what do you think ?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can do something like this (although you can't have a field equal a recordset) - but use a join so instead of

    Code:
    SELECT field1
    FROM tb
    WHERE field5 = (SELECT field4 as fname
                             FROM tb2
                             WHERE field2 = 5)
    you would have

    Code:
    SELECT tb.field1
    FROM tb INNER JOIN tb2 ON tb.field5=tb2.field4
                             WHERE tb2.field2 = 5

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    An sql string that is '20 pages'?

    Assuming the inner SQL would return more than one record, the first SQL syntax would use IN instead of =.

    SELECT field1
    FROM tb
    WHERE field5 IN (SELECT field4 FROM tb2 WHERE field2 = 5)
    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.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    One trick I have used for this is to create a "Variables" table, with only one record in it, and fields to match your variables. Include that table in your query's FROM part, and you can then reference any of its values with something like tb.field5 = tblVariables.VariableName for example. Since it has only one record, you don't need to worry about a JOIN to it.

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

Similar Threads

  1. Variables In VBA
    By jo15765 in forum Programming
    Replies: 4
    Last Post: 12-15-2016, 08:19 AM
  2. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 2
    Last Post: 12-25-2015, 05:44 PM
  3. Replies: 8
    Last Post: 09-19-2013, 06:22 AM
  4. VBA variables in SQL
    By compooper in forum Programming
    Replies: 3
    Last Post: 07-06-2011, 11:04 AM
  5. sql in vb variables
    By emilylu3 in forum Programming
    Replies: 3
    Last Post: 03-04-2006, 01:26 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