substitute and replace functions

[MARKNLA2]

how do i use the substitute and replace functions from the query design grid? can the be used as expressions in the field row or criteria row? what about within an update or append query?

marknla2

 

[tlbroadbent]

You can use functions in the Field, Criteria and Update To rows of the designer grid. You can use functions in SELECT, UPDATE, MAKE TABLE and APPEND (INSERT) queries.

NOTE: The replace function isn't recognized in a Jet Query. However, you can create a public user defined function to wrap around the Replace function.

User Defined Replace Function:

Public Function fnReplace _
(sString As String, sFind As String, _
sReplace As String) As String

fnReplace = Replace(sString, sFind, sReplace)

End Function

Select Example: Replace any occurrence of Tom with Jerry

Select
EmpName,
fnReplace(EmpName, "Tom", "Jerry&quot
From tblEmployees
Where Instr(EmpName, "Tom&quot > 0

Update Example: Update SSN replacing dashes with an empty string and converting employee name to proper case (using StrConv).

Update tblEmployees
Set SSN=fnReplace(SSN, "-", "&quot,
EmpName=strConv(EmpName, 3)

Criteria Example: Suppose tblEmployees has dashes in the SSN and tblTemp does not have dashes. Further, suppose you want to find which rows in tblEmployees exist in tblTemp.

Select * From tblEmployees
Where fnReplace(SSN, "-", "&quot
In (Select SSN From tblTemp) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.