replace string

[Brianfree]

Hi, i have a field in a table which has values like...

934881
925676
946789
etc...


i want to be able to display the results in a query like the following...

DRA4881
DRB5676

Basically, if the first two digits are '93' then replace with
'DRA' then the last four digits etc...

The relationship is...

93xxxx = DRAxxxx
94xxxx = DRBxxxx
95xxxx = DRKxxxx
96xxxx = DRFxxxx

Please can anyone post an example?

Many thanks,

BF

 

[dhookom]

I'm not sure why posters don't provide table and field names :-(

Start by creating a table that models your relationship. You can then add the table to a query with your original table
Your criteria might look like:
WHERE Left(Field1,2) = NewFieldA

Your column/expression might look like:
NewFieldB & Mid(Field1,3)



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
I understand your pain. I built this module to make it easy on the OPs. If you like it you may want to reference this as a good way to document tables and relationships. I know there are some more detailed documenters but this is simple and small and does a good job for posting table relationships to tek-tips.

FAQ700-6905

 

[dhookom]

MajP,
I like it and it's very conveniently here in an FAQ.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

If you always have 6 digits maybe a giant nasty nested iif statement would work as well.

select stuff, more_stuff, iif(left(field1,2)=93, "DRA"+ right(field1,4),iif(left(field1,2)=94, "DRB"+ right(field1,4), iif(left(field1,2)=95, "DRK"+ right(field1,4) etc etc


I won't even attempt to close those and add the correct pararentheses.

 

[randy700]

iif(left(field1,2)=93, "DRA",iif(left(field1,2)=94, "DRB", iif(left(field1,2)=95, "DRK"))) + right(field1,4)

Randy

 

[BigRed1212]

Yeah- like that. What I was trying to suggest but done elegantly.

 

[dhookom]

IMO, nested IIf()s is the poor practice. We don't know if the list of values is complete, if there will be more values in the future, if the values will change over time, etc.

You shouldn't have to edit/maintain a "giant nasty nested iif statement". This is a database tool. You should use it like one and maintain data, not expressions.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

IMO, nested IIf()s is the poor practice. We don't know if the list of values is complete, if there will be more values in the future, if the values will change over time, etc.

Agreed. Thus the qualifiers "nasty" and such. It was just an idea given what we know. Your solution is better.

 

[dhookom]

I didn't mean to sound so critical but I have a crusade against nested IIf()s, poor naming of objects, use of lookup fields, un-normalized tables, and a bunch of other stuff that are pet-peeves

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

The biggest "converter" to all the good stuff you talk about in my limited experience is actually doing it. This site pulled me from out of the darkness of nonnormalized tables into the light of subqueries and inner joins. Having done it, I'm not going back if I can avoid it.

Suggesting a nested iif is probably just the evil side of a reformed practitioner creeping through.

 

[Golom]

In keeping with Duane's and BigRed's comments ... try something like

[tt]tblPrefix
Number Prefix
93 DRA
94 DRB
95 DRK
96 DRF
[/tt]

then

Select (P.Prefix & Mid(M.Code,3)) As NewCode

From myTable As M INNER JOIN tblPrefix As P
     ON Val(Left(M.Fld,2)) = P.Number

Where "Fld" is the name of the field containing 934881, 925676, 946789, ... etc.