Is there any way to write a function that could be run in a query on a single
column that would modify all textual columns without including the column names
as input parameters?
Example: Table with [last], [first], [middle], [address], [date]
Update - results would be MyUcase of [last], [first], [middle], and [address].
Yes there is such a way:
- create a function:
Function MyChangeCase(byval vstrTableName as string, _ byref rdbs as database) dim tdf as tabledef dim fld as field dim strSql as string dim blnTextFldsFound as boolean strSql = "update [" & vstrTableName & "] set " blnTextFldsFound = False set tdf = rdbs.tabledefs(vstrTableName) for each fld in tdf.fields if fld.type = dbText then strSql = strSql & "[" & fld.name & "] = MyUCase([" & fld.name & "]," blnTextFldsFound = True endif next if blnTextFldsFound then strSql = Left(strSql, len(strSql)-1) rdbs.execute strSql endif end function
or you can use recordet the similar way invetigating its Fields collection.
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.