function "Replace"

[sheela]

Hi,

I need to get results from a select query. One of the
fields has the values in the format"nnn-nn-nnn-n"
I need the result to display with out the hyphens in the
format "nnnnnnn".

I am using the "replace" function the select query as,
SELECT Replace(

![Field],"-","") AS Expr1
FROM Table;

But getting a compile error, when try to run the query.
Can some one point me in correct direction?
Thanks in advance,
sheela

 

[Jen]

Hi Sheela,

Should be

SELECT Replace([Field],"-", "") as Expr1 FROM

;

 

[JL]

I am using the "replace" function the select query as,

SELECT Replace(

![Field],"-","") AS Expr1
FROM Table;

Access think that you are calling a function
called "replace". There is a "replace" function but only
will work in VBA (I think).

So, if you write a function in "Modules"
called "replace_hyphon" or something. It will work.

 

[sheela]

Jen:
I tried it, but not working.
Sheela

-----Original Message-----
Hi Sheela,

Should be

SELECT Replace([Field],"-", "") as Expr1 FROM

;

-----Original Message-----
Hi,

I need to get results from a select query. One of the
fields has the values in the format"nnn-nn-nnn-n"
I need the result to display with out the hyphens in the
format "nnnnnnn".

I am using the "replace" function the select query as,
SELECT Replace(

![Field],"-","") AS Expr1
FROM Table;

But getting a compile error, when try to run the query.
Can some one point me in correct direction?
Thanks in advance,
sheela

.

.

 

[John Spencer (MVP)]

Which version of Access are you using? Access 97 does not have a replace
function. Access 2000 has a replace function, but it does NOT work in queries.
Replace works in later queries.

Quote from Dirk Goldgar, MS Access MVP

The Replace function works in Access 2000 VBA, but it can't be used directly
in queries. You need to create a user-defined "wrapper" function that
receives arguments of the appropriate types, passes them on to the Replace
function, and returns the Replace function's result. Here's one:

'---- 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 ----

I don't know offhand whether this problem has been fixed in Access 2002.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

ALSO, if the field is ALWAYS in the format you specified, then you could use the
string functions. To generate the value minus the hyphens

Mid(FieldName,1,3) & Mid(FieldName,5,2) & Mid(FieldName,8,3) & Mid(FieldName,10,1)