Re: Repace function in a query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:eRi88Rc%23FHA.912@xxxxxxxxxxxxxxxxxxxxxxx
> 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>
>
> "franky" <franky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:E412037B-9276-44EE-BD7B-51291DB807D3@xxxxxxxxxxxxxxxx
>> 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
>
>


.

Relevant Pages

  • ADO exception with character combination inside string...
    ... We also have various Delphi7 programs that operate on this database. ... These programs are set up to trap the exception in an ADO operation ... and log the query text that was in use when the exception happened. ... What happens is that if we have a string to store inside the database ...
    (borland.public.delphi.database.ado)
  • Row Level Locking from Excel VBA query
    ... There are multiple users accessing the database at ... query that I am using so that two users will not get the same order ... Dim dbsConn As ADODB.Connection ... Dim connString As String ...
    (microsoft.public.access.queries)
  • Changing ODBC Connection between development and production
    ... Public Function ExecAgingReport(datToDate As String, ... Set db = CurrentDb ' Setting the database as current Database ... pass-through query ... qd.ReturnsRecords = True ' Query does retrieve records ...
    (microsoft.public.access.modulesdaovba)
  • Re: sql select statement not working
    ... The single quotes go around the value, concatenated into the string: ... >>> select that order form the database. ...
    (microsoft.public.outlook.program_forms)
  • Re: javascript in a querys text string
    ... Now, can you please explain to me how to limit the contents of the column shown the text string so, only the cells where the record's.Value Is Null? ... double each internal double-quote, and don't change the single quotes, ... page) there is a query displaying the first four fields. ... The general idea is that I don't know how to replace/escape the ...
    (comp.databases.ms-access)