Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: using a variable instead of fieldname in query SQL

    Hi guys,

    I am new to the Access world (but less to the SQL, coming from the GIS world) and as this is my first post I'd like to thank you all as I've found this forum to be very very helpful in learning how to use Access.

    I have an Access 2003 database that documents orders of supplies for an electricity store. The database (named "work_table") has a few fields such as OrderNumber, OrderDate, InCharge (housing the name of the staff member who made the order), Firm (housing the name of the firm the order was made from), Cable1Num (housing the number of Cable1 units that were ordered in this specific order), Cable2Num (housing the number of Cable2 units that were ordered in this specific order) and so on ( I have 11 such numeric fields, one for each type of inventory).

    I am trying to build a report that will give the manager of the store a full picture of his supply orders. I want that in the query leading to the report, the manager will be able to define a range of dates and a inventory name (which means 2 textboxes for the FromDate and ToDate and a combobox for the inventory type) and the report will give all orders of that inventory in that period of time.

    My basic SQL query was this:

    SELECT work_table.*
    FROM work_table
    WHERE (((work_table.Cable1Num)>0) And ((work_table.OrderDate) Between Forms!reportform2!startdate And Forms!reportform2!enddate));

    The above sql query is good if I have one or two types of inventories, since every query (and thus the report leading to it) is unique for that inventory. As I have eleven of those, and I don't want to fill my mdb with too many queries and reports, I am looking for a different solution.

    MY QUESTION:

    Is it possible to put a field name variable in the basic sql that will update according to a combobox value?

    Something like:

    SELECT work_table.*
    FROM work_table
    WHERE (((work_table.X)>0) And ((work_table.OrderDate) Between Forms!reportform2!startdate And Forms!reportform2!enddate));

    The identity of the fieldname X (field name, not field value) in query will be changed according to the combobox chosen by the user(Cable1Num, Cable2Num or so on) thus enabling me to write only one query and one report.

    Is this type of thing possible? If so, could anyone lend me a hand with syntax or with a example database?

    I looked hard for this type of thing (which seems trivial), but as the query SQL seems very conservative and the query-report linkage in Access seems rigid, I don't know if this type of thing exists.

    Thanks in Advance,

    Adnaket (otherwise known as Jonathan)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not directly with a variable, but you can do this easily with a public function.
    1. Create a module in the database.
    2. Paste the following code in this new module:
    Code:
    Option Compare Database
    Option Explicit
    
    Public var As Variant
    
    Public Function GetVar() As Variant
    
        GetVar = var
        
    End Function
    3. You can now create a query (Query1) that will use the GetVar function:
    Code:
    SELECT Tbl_Sales.Sales_Date, Tbl_Sales.Sales_Amount
    FROM Tbl_Sales
    WHERE (Tbl_Sales.Sales_Amount>getvar());
    4. From anywhere in your application, you can now use some code such as:
    Code:
    var = 100
    docmd.OpenQuery "Query1"
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    Thanks, but it is wasn't exactly what I meant.

    First –thanks for the reply.

    If I understand correctly, the code you posed lets the user define the numeric value of the field a priory to the query, In your example to tell the query to show only the orders where the amount field value is above 100 (which is the value of var). That is super-useful(!) and will help me much later on, but right now I am looking for a different thing –

    I want the query to be able to change the field identity according to a form combo – In your code – to change the reference to " Tbl_Sales.Sales_Amount" to Tbl1_Sales.SalesCable1 Or Tbl1_Sales.SalesCable2, according to the field name chosen in the combo.

    (Intuitively I'd say that it would have to be something like

    SELECT Tbl_Sales.*
    FROM Tbl_Sales
    WHERE (Tbl_Sales.X == form!combobox!());
    But it is not possible and a bit messed up…

    Thanks a million,

    Adnaket

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A SQL query is otherwise known as a SQL Statement. You can't parameterise objects or clauses in a statement.

    The first issue is that this suggests a repeating groups problem which is often (though not unarguably) considered a violation of first normal form. At the very minimum it is an alarm that the design is likely wrong. The very fact you are trying to write a query like this confirms that this is not a good design.

    Please read this and pay attention especially to first normal form
    Fundamentals of Relational Database Design -- r937.com
    As you can see, the query is trivial if the table is designed correctly in the first place.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always edit the SQL statement before opening the query.

    1. The query Query1 (notice the brackets):
    Code:
    SELECT Tbl_Sales.FK_Tbl_Customers, 
        Tbl_Sales.FK_Tbl_Invoices, 
        Tbl_Sales.Sales_Received, 
        Tbl_Sales.Sales_Text, 
        Tbl_Sales.Sales_Amount, 
        Tbl_Sales.Sales_VAT_Rate
    FROM Tbl_Sales
    WHERE [FK_Tbl_Invoices] Is Null;
    2. The code in the form module:
    Code:
    Private Sub Command_OpenQuery_Click()
    
        Dim qdf As DAO.QueryDef
        Dim strFieldName As String
        Dim intStart As Integer
        Dim intRightBracket As Integer
        
        Set qdf = CurrentDb.QueryDefs("Query1")
        intStart = InStr(qdf.SQL, "[") + 1
        intRightBracket = InStr(qdf.SQL, "]")
        strFieldName = Mid(qdf.SQL, intStart, intRightBracket - intStart)
        qdf.SQL = Replace(qdf.SQL, strFieldName, Me.Combo_Fields.Value)
        DoCmd.OpenQuery "Query1"
        Set qdf = Nothing
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Apr 2010
    Location
    Phoenix, AZ
    Posts
    1

    Cool

    Ok... What I would do would be to build the SQL statiement in the code, then put it into the query that will be running the report.

    If the report only has one inventory type, then make the datasource generic... like 'InventoryNum' or whatever...

    this code would be behind a button on your form. there would be a combo box or list box that would show the different inventory types that you want the report to show. I'll use the name cboInventory as the list.
    -----------
    dim qry as Querydef
    dim db as Database
    dim strSQL as string

    set db = currentdb

    strsql = "SELECT OrderNumber, OrderDate, InCharge, Firm, [" & me.cboinventory.value & "] as InventoryNum " & _
    "FROM work_table " & _
    "WHERE ((([" & me.cboinventory.value & "])>0) And ((OrderDate) Between me.startdate And me.enddate));"

    set qry=db.querydef("qryReportQuery")
    qry.sql = strsql
    qry.close
    set qry = nothing

    docmd.openreport "ReportName", vbPreview

    ----
    This way, you can have any of the different inventory fields used. this could also be used for multiple inventory lists...

    GwydionC

  7. #7
    Join Date
    Mar 2012
    Posts
    2

    Error message when entering SQL code

    Hello,

    Where do I have to build the SQL statement in?

    When I put the SQL code in the SQL viewer of the query, MS Access returns error messages "Invalid SQL statement expected Delete, Insert"

    Regards,

    Eelko van der Wal

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not precise which of the former answers causes the problem you're facing. Anyway, all these answers stated that you need to use some VBA code (in the class module of a Form or in an independent module). None of these mention using the SQL viewer of the query:
    Quote Originally Posted by Sinndho View Post
    Not directly with a variable, but you can do this easily with a public function.
    1. Create a module in the database.
    2. Paste the following code in this new module:
    Quote Originally Posted by Sinndho View Post
    You can always edit the SQL statement before opening the query.
    2. The code in the form module:
    Quote Originally Posted by Gwydionc View Post
    Ok... What I would do would be to build the SQL statiement in the code, then put it into the query that will be running the report...
    this code would be behind a button on your form.
    Please explain which solution you're trying to use and how.
    Have a nice day!

  9. #9
    Join Date
    Mar 2012
    Posts
    2
    Sinndho,

    I refer to the solution of Gwynionc.

    Eelko

  10. #10
    Join Date
    May 2012
    Posts
    1
    Thanks a lot bro, really helpful!

    Quote Originally Posted by Sinndho View Post
    Not directly with a variable, but you can do this easily with a public function.
    1. Create a module in the database.
    2. Paste the following code in this new module:
    Code:
    Option Compare Database
    Option Explicit
    
    Public var As Variant
    
    Public Function GetVar() As Variant
    
        GetVar = var
        
    End Function
    3. You can now create a query (Query1) that will use the GetVar function:
    Code:
    SELECT Tbl_Sales.Sales_Date, Tbl_Sales.Sales_Amount
    FROM Tbl_Sales
    WHERE (Tbl_Sales.Sales_Amount>getvar());
    4. From anywhere in your application, you can now use some code such as:
    Code:
    var = 100
    docmd.OpenQuery "Query1"

Posting Permissions

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