Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    varaible in SQL (Access 2000)

    Can I use a variable in an sql code ?

    I have the following function:

    Public function size()
    Dim StrSQL As string

    StrSQL = " SELECT qtotallitersbase.CompanyName, Sum(qtotallitersbase.SumOfliters) AS SumOfSumOfliters, qtotallitersbase.afid " & _
    " FROM qtotallitersbase " & _
    " GROUP BY qtotallitersbase.CompanyName, qtotallitersbase.afid " & _
    " ORDER BY Sum(qtotallitersbase.SumOfliters) DESC;"

    qtotallitersbase is a query on which I base my query. My question is, can I dim a second variable ,called

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: varaible in SQL (Access 2000)

    >>qtotallitersbase is a query on which I base my query. My question is, can I dim a second variable ,called
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: varaible in SQL (Access 2000)

    If you just want to make your SQL more readable, you can use an alias, which would change your SQL to this:

    Sum(secq.SumOfliters) AS SumOfSumOfliters, secq.afid " & _
    " FROM qtotallitersbase AS secq " & _
    " GROUP BY secq.CompanyName, secq.afid " & _
    " ORDER BY Sum(secq.SumOfliters) DESC;"
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: varaible in SQL (Access 2000)

    If what you are trying to do is to create a SQL statement that uses a variable to define one of multiple sources of data that you want to run the SQL statement against, the answer is YES. Try the following:

    Dim StrSQL As string
    Dim secq as String

    StrSQL = " SELECT CompanyName, Sum(SumOfliters) AS SumOfSumOfliters, afid " & _
    " FROM " & secq & _
    " GROUP BY CompanyName, afid " & _
    " ORDER BY Sum(SumOfliters) DESC;"

    I've eliminated the table qualifiers from all field names (they are only needed when you have more than one table in a query) and concatenated the FROM statement with the name contained in the secq variable.

    The reason your query did not work is because you were including the VARIABLE name in the FROM string, instead of concatenating its value. Access was trying to look for a table or query names "secq"!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •