Repace function in a query

[Guest]

Hello,

I'm attempting to use the Replace function in a query and get a "Function
Undefined" error when running query. However, the Replace function is
working fine in a module and debug window in the same database.

I have some text fields with double quotes in them (ie: like "this") and I
wanted to replace them with single quotes. I cant seem to pass these values
to a function because I get a "Expected: list separator or )" error because
of the double quotes in the string. Any suggestions?

Thanks in advance

 

[Jeff Boyce]

How? We're not there, so we can't see how you are trying "to use the
Replace function in a query". Can you post the SQL statement for the query?

Are you certain there are no References marked as MISSING in your database?

Regards

Jeff Boyce
<Office/Access MVP>

 

[fredg]

Hello,

I'm attempting to use the Replace function in a query and get a "Function
Undefined" error when running query. However, the Replace function is
working fine in a module and debug window in the same database.

I have some text fields with double quotes in them (ie: like "this") and I
wanted to replace them with single quotes. I cant seem to pass these values
to a function because I get a "Expected: list separator or )" error because
of the double quotes in the string. Any suggestions?

Thanks in advance

1) Directly in your query try:
NewColumn:Replace([FieldName],Chr(34),Chr(39))

or
NewColumn(Replace([FieldName],"""","'")

for clarity that's ([FieldName]," " " "," ' ")

If that still gives an Undefined Function error, then:

2) In earlier versions of Access that have the Replace function you
cannot use it directly in an Access query.
You need to create a module (as you have) and then refer to the module
function rather than use the Replace function directly in a query.

In other words create a function in a Module:
Function GetRidOf(FieldIn as String) as String
Replace(FieldIn,"""","'")
End Sub

Call it from the query:
NewColumn:GetRidOf([FieldName])

 

[John Spencer]

What version of Access? Access 2000 has the Replace function, but it
doesn't work in queries as the expression service does not recognize it.
I'm not sure, but I think it was patched in one of the updates to Access
2000.

You can use a wrapper function to make it work.

Code from Dirk Goldgar, MS Access MVP

'---- start of code ----
Function fncReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1, _
Optional lngCompare As Long = vbBinaryCompare) _
As String

fncReplace = Replace(strExpression, strFind, strReplace, _
lngStart, lngCount, lngCompare)

End Function
'---- end of code ----


www.datagnostics.com

 

[Guest]

Thanks for the response

I've tried:
SELECT tblsonoBids.sonoDesc, Replace([sonoDesc],Chr(34),Chr(39)) AS NewString
FROM tblsonoBids;

and

SELECT tblsonoBids.sonoDesc, Replace([sonoDesc],"A", "B") AS NewString
FROM tblsonoBids;

When I attempt this in the debug window, it works:
Debug.Print Replace("Testing ABC", "A", "X")
Testing XBC

The references are:
Visual basic for applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library


Thanks!

 

[Guest]

Thanks for the response
I've attempted that but I cant pass the results of the query to the function
because I get "Expected: list separator or )" error being caused by the
double quotes in the values of the query.

Errors when attempting to pass value to function.
Public Function ReplaceText(inputString as string) as string

Hello,

I'm attempting to use the Replace function in a query and get a "Function
Undefined" error when running query. However, the Replace function is
working fine in a module and debug window in the same database.

I have some text fields with double quotes in them (ie: like "this") and I
wanted to replace them with single quotes. I cant seem to pass these values
to a function because I get a "Expected: list separator or )" error because
of the double quotes in the string. Any suggestions?

Thanks in advance

1) Directly in your query try:
NewColumn:Replace([FieldName],Chr(34),Chr(39))

or
NewColumn(Replace([FieldName],"""","'")

for clarity that's ([FieldName]," " " "," ' ")

If that still gives an Undefined Function error, then:

2) In earlier versions of Access that have the Replace function you
cannot use it directly in an Access query.
You need to create a module (as you have) and then refer to the module
function rather than use the Replace function directly in a query.

In other words create a function in a Module:
Function GetRidOf(FieldIn as String) as String
Replace(FieldIn,"""","'")
End Sub

Call it from the query:
NewColumn:GetRidOf([FieldName])

 

[Guest]

Thank you! I am using Access 2000 SR1. Your wrapper function worked great.

Many thanks to all responders!!!!