Multiple Replace([ in Access QBE ?

[TommyT]

Can I use the replace function more than once in a query return?


I have a column from a long query spqnning 2 datasource. 1 column
[enroll] returns

enroll

I
S
I
D
D
I
S
D
I
C
S
D
I
I
C
D


I need to replace the I's with 6's and remove the D's, S's and
C's
I can replace the I's with 6's using

Expr1: Replace([enroll],"I",6)

In the query, but cannot replace/remove the rest?

The query works as it should,
returning the fields from 2 differnt datasources, SQL2000 and Access.
However, I need to replace the [enroll] return values. The codes we use

are I,S,D,C The return values are "I", "S", "D", and "C". I am using,

Expr1: Replace([enroll],"I",6)
to replace the "I" to a 6 in the Access QBE. The problem I have is
that I need this replace function to also replace the "S", "D", and "C"

with " " Which would reslut in an empty space. Is this possible? I saw
VBA script using replace(replace(replacereplace, but I cannot get the
syntax correct to do multiple replaces, and not sure if this is even
possible?

Any suggestions?

 

[fredg]

Can I use the replace function more than once in a query return?

I have a column from a long query spqnning 2 datasource. 1 column
[enroll] returns

enroll

I
S
I
D
D
I
S
D
I
C
S
D
I
I
C
D

I need to replace the I's with 6's and remove the D's, S's and
C's
I can replace the I's with 6's using

Expr1: Replace([enroll],"I",6)

In the query, but cannot replace/remove the rest?

The query works as it should,
returning the fields from 2 differnt datasources, SQL2000 and Access.
However, I need to replace the [enroll] return values. The codes we use

are I,S,D,C The return values are "I", "S", "D", and "C". I am using,

Expr1: Replace([enroll],"I",6)
to replace the "I" to a 6 in the Access QBE. The problem I have is
that I need this replace function to also replace the "S", "D", and "C"

with " " Which would reslut in an empty space. Is this possible? I saw
VBA script using replace(replace(replacereplace, but I cannot get the
syntax correct to do multiple replaces, and not sure if this is even
possible?

Any suggestions?

Replace(Replace(Replace(Replace([YourField],"I","6"),"S",""),"D",""),"C","")

Any reason you can't use a simple IIF expression:
NewColumn:IIf([YourField] = "I",6,"")

 

[Douglas J. Steele]

Expr1: Replace(Replace(Replace(Replace([enroll],"I",6), "S", " "), "D", "
"), "C", " ")

 

[TommyT]

Thanks!!! Works Great!!

Expr1: Replace(Replace(Replace(Replace([enroll],"I",6), "S", " "), "D", "
"), "C", " ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can I use the replace function more than once in a query return?


I have a column from a long query spqnning 2 datasource. 1 column
[enroll] returns

enroll

I
S
I
D
D
I
S
D
I
C
S
D
I
I
C
D


I need to replace the I's with 6's and remove the D's, S's and
C's
I can replace the I's with 6's using

Expr1: Replace([enroll],"I",6)

In the query, but cannot replace/remove the rest?

The query works as it should,
returning the fields from 2 differnt datasources, SQL2000 and Access.
However, I need to replace the [enroll] return values. The codes we use

are I,S,D,C The return values are "I", "S", "D", and "C". I am using,

Expr1: Replace([enroll],"I",6)
to replace the "I" to a 6 in the Access QBE. The problem I have is
that I need this replace function to also replace the "S", "D", and "C"

with " " Which would reslut in an empty space. Is this possible? I saw
VBA script using replace(replace(replacereplace, but I cannot get the
syntax correct to do multiple replaces, and not sure if this is even
possible?

Any suggestions?