Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014

    Unanswered: Filtering a set of criteria from different field of same table

    Hi All,

    I am new to access and would like to seek advice from you guys on how can I go about doing this. I have a table with 4 field (ColumnA, ColumnB, ColumnC and ,ColumnD). Within each field, is a drop-down (A to E) input linked from another table with a single field called Column(which also the primary key of the table). How do I create a query / SQL so that it will select out the row that contain, the parameter are set from a user input, from each column.

    For example, I would like to see the list ID that contain letter A and E in any of the ColumnA to ColumnD.

    Attached Thumbnails Attached Thumbnails DB_Table.jpg  

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    build your sql query on the fly, or set parameters to a pre existing query (a stored procedure)

    for writing the query in the fly
    lets say you have 4 combos cmbA,cmbB,cmbC and cmbD

    dim strWhere as string
    dim strSQL as string
    strWhere = " where 1=1" 'A SQL trick that ensure the where clause will always be valid
    if len(cmbA.text)>0 then
    strwhere = strwhere & " AND columnA ='" & cmbA.text & "'"
    if len(cmbC.text)>0 then
    strwhere = strwhere & " AND columnC ='" & cmbC.text & "'"
    if len(cmbD.text)>0 then
    strwhere = strwhere & " AND columnD ='" & cmbD.text & "'"
    if len(cmbB.text)>0 then
    strwhere = strwhere & " AND columnB ='" & cmbB.text & "'"

    strSQL = "SELECT my, column, list from mytable " & strWhere
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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