replace?

[lionel]

Hi,

How to put replace function in a query?

SELECT DISTINCT
ID, Price ,Street FROM MyPrice
WHERE City = Replace(myCity, "'", "''")

myCity is a variable name.


Thanks...

 

[Tom Ellison]

Dear Lionel:

You say, "myCity is a variable name." Do you mean this is a variable
accessable in your VBA code?

If so, I would suggest you create a public function that returns:

Replace(myCity, "'", "''")

Then use this function in your query:

SELECT DISTINCT ID, Price ,Street FROM MyPrice
WHERE City = YourFunction()

You cannot reference VBA variables directly in a query, but you can
return the value in or derived from your variable using a public
function, then use that value in the query.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[lionel]

SELECT DISTINCT ID, Price ,Street FROM MyPrice
WHERE City = YourFunction()

You cannot reference VBA variables directly in a query, but you can
return the value in or derived from your variable using a public
function, then use that value in the query.

Thaks Tom,

How to make a public function(YourFunction()) in my MDB and call it
directly?

SELECT DISTINCT ID, Price ,Street FROM MyPrice
WHERE City = YourFunction()

 

[Tom Ellison]

In a new module, type;

Public Function YourFunction() As String
YourFunction = Replace(myCity, "'", "''")
End Function

If myCity is not a public variable, you may have to put this function
into the module where myCity is available.

The fact that you did not know how to program this makes me wonder
what myCity is? Is it a parameter to the query perhaps? If so, then
please explain the problem you had originally. My solution was for a
variable called myCity, not for a parameter.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

Hi,

How to put replace function in a query?

SELECT DISTINCT
ID, Price ,Street FROM MyPrice
WHERE City = Replace(myCity, "'", "''")

myCity is a variable name.

This will work in Access2002, but for some reason in Access2000 the
Replace function can't be used from a query. The getaround is to write
a silly little wrapper function:

Public Function QReplace(sIn As String, sOld As String, _
sNew As String) As String
QReplace = Replace(sIn, sOld, sNew)
End Function

and then use QReplace instead of Replace in your query.