Looking for a more clever SQL WHERE clause

[scooterm]

### PROBLEM
I am looking for a more 'clever' way to put together a SQL
WHERE clause in msft access, so that I do not have to re-write
a particular SQL query whenever the fields in a table are
changed.

### DETAILS
I have a very simple query that looks like this:

SELECT *
FROM person
WHERE
fname = 'simpson'
OR lname = 'simpson'
OR mname = 'simpson'
OR sname = 'simpson'

What I would *really* like to do is (even though this is not
valid SQL):

SELECT *
FROM person
WHERE
any_text_field = 'simpson'

### QUESTION
Is there a clever way (using pure SQL in msft access) to obtain
the simplicity of the psuedo-SQL statement shown above?

### RATIONALE
The reason why I want to do this is: 1) I want to be able to change
the table without having to re-write the SQL query; 2) I don't really
care which field contained the value 'simpson'; 3) I don't want to
worry about error messages of 'mismatched type' from trying to evaluate
for 'simpson' on a field that is something other than a 'Text' field.

**Please** feel free to refrain from a critique of the underlying
rationale. I know the critique already, but this is a unique scenario,
just looking for an answer and a clever example that I may not have
already thought of.

 

[Duane Hookom]

I would generally suggest SQL like:

WHERE FName & LName & MName & SName Like "*Simpson*"

This would not accomodate the addition of new fields. I am not aware of any
Access SQL solution that would automatically include added fields. I suppose
you don't want to hear opinions on why fields should rarely if ever be added
to tables...

 

[Douglas J. Steele]

There's nothing in SQL that will let you do that, but you could dynamically
generate the SQL query.

Something like the following untested aircode should work:

Function GenerateSQL( _
TableName As String, _
NameToFind As String _
) As String

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String
Dim strWhere As String

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
strSQL = "SELECT * FROM MyTable "
For Each fldCurr In tdfCurr.Fields
If fldCurr.Type = dbText Then
strWhere = strWhere & fldCurr.Name & _
" = " & Chr$(34) & NameToFind & _
Chr$(34) & " Or "
End If
Next fldCurr

If Len(strWhere) > 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 4
GenerateSQL = strSQL & "WHERE " & strWhere
Else
GenerateSQL = strSQL
End If

Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing

End Function

Note that you probably don't want to use single quotes in your SQL
statement, since you'll run into problems with names like O'Connor.

 

[Guest]

If you want to change fields frequently, create a name table:
put an idx or "fieldName" column in the table

1 1 1 "fname" "fred"
2 1 2 "lname" "simpsom"
3 1 3 "sname"
4 2 1 "fname" "tan"
5 2 2 "lname" "leng"
6 2 3 "sname" "tan"

If you just want to change the "names" of the fields
frequently, you have two choices: you can change the
names of the fields in the table, and use a source query
to translate the field names to your own invariante
names, which you can then use everywhere without
change (just change the source query when the table
changes) OR you can leave the table field names invariant
(strField1, strField2, strField3), which you can use everywhere
without change and use a source query to map those field
names to whatever it is they should be called this week

a mapping query looks like this:
select strfield1 as sname, strfield2 as lname

or like this:
select sname as strfield1, lname as strfield2

in the query design window it looks like this:
strfield1: sname
or like this
sname: strfield1

(david)

 

[Michel Walsh]

Hi,


WHERE "Simpson" IN(Fname, Lname, Mname, Sname )



The IN list must be typed in advance, it cannot be IN(*).




Hoping it may help,
Vanderghast, Access MVP