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

    how to convert query into sql (Access 2000)

    I have a query made up from three queries, namely qryOnOrder,qryInflow and qrySold.My query is called qryStockFlow.
    It is the following:
    StrStockFlow = " SELECT qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size, qryOnOrder.SumOfcartons AS OnOrder,

    qrySold.SumOfcartons AS Sold, qryInflow.SumOfcartons AS Inflow " & _
    " FROM (qryOnOrder INNER JOIN qrySold ON qryOnOrder.Productid = qrySold.Productid) INNER JOIN qryInflow ON

    qrySold.Productid = qryInflow.Productid " & _
    " GROUP BY qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size, qryOnOrder.SumOfcartons, qrySold.SumOfcartons,

    qryInflow.SumOfcartons"

    In order to reach greater felxibility, i want to replace the name of the queries with the name of an SQL.
    For example i have converted the query qryOnOrder into an sql sqlOnOrder .However when i replaced qryOnOrder with
    StrOnOrder, i received an error and a message The Microsoft jet cannot find the query StrOnStock.
    The following codes gives me the error message:

    strStockFlow = " SELECT StrOnOrder.Productid, StrOnOrder.grade, StrOnOrder.size, StrOnOrder.SumOfcartons AS OnOrder,

    qrySold.SumOfcartons AS Sold, qryInflow.SumOfcartons AS Inflow " & _
    " FROM (StrOnOrder INNER JOIN qrySold ON StrOnOrder.Productid = qrySold.Productid) INNER JOIN qryInflow ON

    qrySold.Productid = qryInflow.Productid " & _
    " GROUP BY StrOnOrder.Productid, StrOnOrder.grade, StrOnOrder.size, StrOnOrder.SumOfcartons, qrySold.SumOfcartons,

    qryInflow.SumOfcartons"


    I tried to put the StrOnOrder in bracked, like (StrOnOrder) but then i receive the message Invalid use of ()

    May i ask for help to see where my mistake is?

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

    Re: how to convert query into sql (Access 2000)

    I'm sorry, but I don't quite understand what you mean by replacing your query names "with the name of an SQL." Queries ARE SQL. Are you talking about creating a SQL string asigned to a variable and trying to use that variable in another query or SQL string? You can't do that. The query engine simply can't resolve variables, so you're going to have to find another way. The simplest is to save what you're now trying to assign to a variable to a saved query and use the name of the saved query.
    Charlotte

Posting Permissions

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