01-23-14, 04:07 #1Registered User
- Join Date
- Jan 2014
Unanswered: Filtering a set of criteria from different field of same table
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.
01-23-14, 04:49 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 " & strWhereI'd rather be riding on the Tiger 800 or the Norton