SUBSTITUTE FUNCTION IN ACCESS 97

[jane30]

Hi, is there a function in Access 97 same as the Substitute function in Excel 97? The reason I need this is that I have more than 10 select queries. I need to modify the table name in them. So, I'm thinking about using "For each qdf in db.Querydfs" this statement to locate the table name in all those queries and substitute all the occurance of that table name with new table name. I was recommended the Replace function() in Access 97. For some reason, I couldn't find it in my Access 97 Help Index. I don't have any third party utilities that allow me to do the find/replace function. Any suggestions about what I should do?

 

[CVigil]

Access97 does not have a Replace function.

But I just read in another post in this forum that Access2000 does.

People should be careful to specify which version of Access they are referring to.

Have a good one. -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[MichaelRed]

[Replace | SUBSTITUTE] are irrelevant anyway. You cannot do this w/o code, and therefor might as well just do the whole process by writting the SQL string verision of the query. You CAN create a 'list' of tables (?queries?) (as an array) either statically or dynamically to use with the process, but what -exactly- you are doing is not clear enough to me to give more specific advice.

At first glance, it looks like you have a set of queries which generate a 'final' recordset (fro a report recordsource?) and you have more than one "table" you want to use as the starting point for the report. To acomplish this, you want to use the ONE set of queries and simply replace the table name(s) of the source(s)?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dpimental]

MichaelRed is right!

The problem is the table name, so attack that problem without getting overly complicated about doing that.

Do this in code ...

2 Considerations ...

If all the queries are the same, with the exception of the table name, then pass the table name as a parameter of the function.

If all the queries are different, you can still either pass the table name, or some such flag, and use an array, a case select, or other such vb mechanism that will allow you to determine which query to run, based on a "flag" you set, and send to this function.

David

 

[bdmclennan]

Here is the code.

Public Sub subQueryReplace()
'=============================================
Dim strSQLIn As String
Dim strSQLOut As String
Dim db As DATABASE
Dim qd As QueryDef
Dim rs As Recordset

Set db = CurrentDb
'====================================================
'This is a list of all your query's you want to update
'======================================================
Set rs = db.OpenRecordset("SELECT distinct Name FROM MSysObjects WHERE MSysObjects.Type=5 and name in('qryTEST')", dbOpenSnapshot)

With rs

Do While Not rs.EOF
strSQLIn = db.QueryDefs(!NAME).sql

'==============================================
'Replace "Old table name" & "new table name"
'==============================================
strSQLOut = fncReplace(strSQLIn, "Old table name", "new table name&quot

'==========================================
'Rename old qry or you can delete - what ever blows your hair back
'docmd.DeleteObject acQuery,rs!NAME
'==========================================
DoCmd.Rename "OLD_" & rs!NAME, acQuery, rs!NAME

'==========================================
'Make your new qry
'==========================================
Set qd = db.CreateQueryDef(!NAME, strSQLOut)

'==========================================
'Move next
'==========================================
rs.MoveNext
Loop

End With
End Sub


Public Function fncReplace(ByVal strSentence As String, varField As Variant, varValue As Variant) As String
Dim strOut As String
Dim lngPos As Long
Dim lngLenValue As Long

lngLenValue = Len(varValue)
lngPos = InStr(1, strSentence, varField)
strOut = strSentence
Do While lngPos > 0
strOut = Left(strOut, lngPos - 1) & varValue
strOut = strOut & Mid(strSentence, lngPos + Len(varField), Len(strSentence) - lngPos + Len(varField))
strSentence = strOut
If lngLenValue > 1 Then
lngPos = InStr(lngPos + lngLenValue, strOut, varField)
Else
lngPos = InStr(lngPos, strOut, varField)
End If
Loop
If Len(strOut) = 0 Then
fncReplace = strSentence
Else
fncReplace = strOut
End If
End Function