Variable parameters in Access Query RRS feed

  • Question

  • I am trying to design a query in VBA that will allow a tolerance parameter on one variable based on another variable.  For example:

    If field 1 is X then field 2 should search everything between A and B

    If field 1 is Y then field 2 should search everything between C and D...

    Field one has forces one of multiple choices setup in a combo box.  I can do the between command in the query design but I can't figure out how to set of the IF/Then statement for field 1.

    I have some VBA programming experience, but i would still consider myself to be a novice.

    • Edited by Laser_R Friday, October 27, 2017 3:32 PM
    Friday, October 27, 2017 3:31 PM

All replies

  • Hi,

    Not sure I'm clear. Are field 1 and field 2 in the same table and are you searching records from the same table where field 1 and field 2 are also located?

    Friday, October 27, 2017 3:35 PM
  • You need to express this in Boolean terms.  I think you might be saying:

    WHERE (Field1 = "X" AND Field2 BETWEEN "A" AND "B")
    OR (Field1 = "Y" AND Field2 BETWEEN "C" AND "D")

    but I'm not completely clear as to your requirements.

    If you are creating a query, substitute appropriate parameters for the constants "X", "Y", "A", "B", "C" and "D"; if you are building the SQL statement in code, concatenate the values of the variables into the statement.

    Ken Sheridan, Stafford, England

    Friday, October 27, 2017 8:20 PM
  • Not sure if I'm understanding you correctly, but I think this may work for you.

    Create another table with three fields Field1Selection, Field2From, Field2To.

    Populate Field1Selection with the values from <yourfirsttable>.Field1 and the other 2 fields with the 'between' values for <yourfirsttable>.Field2.

    Use the new table as the rowsource for the combo box, with columncount=3. Then create the SQL for your query in code like:

    Dim strSql as String

    StrSql = "SELECT * FROM <yourfirsttable> WHERE Field1 = " & <yourcombo> & " AND Field2 BETWEEN <yourcombo>.column(1) & " AND " <yourcombo>.column(2).

    CurrentDB.QueryDefs(<yourquery>).SQL = strSql.

    Saturday, October 28, 2017 10:25 AM