MidString Removal

[scgoodman]

I have data that looks like this '006JFK74901702' and I want to remove the JFK. Sometimes the field can be null.
How do I remove?

 

[boblarson]

How about

Replace([YourFieldName], "JFK", "")

 

[scgoodman]

Thought about that, but not all will be specific to JFK, could be ATL, MIA,etc.

Shannon

 

[Brianwarnock]

And what else haven't you told us, is it always in the same position, if when not null is the string the same length, what do you want if the field is null?

Brian

 

[boblarson]

That isn't language for the forum Shannon. You should probably go modify your post before a mod does. Remember to take into consideration when reading posts that you cannot judge tone from it. I didn't see anything wrong with Brian's questions. Perhaps he might have asked it slightly differently (the first part) but again - when reading written words, you can't assume neccessarily that the intent is negative.

 

[scgoodman]

All I am doing is asking for help. you should think about what you say before you write it. Tone means everything, especially in this setting.

 

[boblarson]

All I am doing is asking for help. you should think about what you say before you write it.

Okay, let's try this again:

1. What letters are likely to come out?

2. Where do they come from? (from another field, user input, what?)

3. What happens if there is a null for that field?

4. Do the letters always fall in the same character location?

(there may be more questions to follow).

 

[rainman89]

Whats up with people coming in here and throwing their attitudes around lately?

 

[boblarson]

Whats up with people coming in here and throwing their attitudes around lately?

It's the economy

 

[raskew]

Oh Brian -

You abusive thing, you!

We're all praying that you'll clean up your evil ways.

Bob

Added:

SCGOODMAN - Are you seeking therapy for this sensitivity issue of yours?

 

[scgoodman]

Sorry, his tone was completely off and I took it the wrong way.

1. What letters are likely to come out? any combination

2. Where do they come from? (from another field, user input, what?) same field.

3. What happens if there is a null for that field? keep null

4. Do the letters always fall in the same character location? yes

 

[scgoodman]

Oh Brian -

You abusive thing, you!

We're all praying that you'll clean up your evil ways.

Bob

Added:

SCGOODMAN - Are you seeking therapy for this sensitivity issue of yours? Aren't we a jokester.

 

[boblarson]

4. Do the letters always fall in the same character location? yes

And a follow up question to that - will it always be 3 letters?

If so, you could use this:

Left([YourFieldNameHere], 3) & Mid(7, [YourFieldNameHere])

 

[Brianwarnock]

You will all be pleased to know that I am sitting in the corner on the naughty chair.

Brian

 

[boblarson]

Left([YourFieldNameHere], 3) & Mid(7, [YourFieldNameHere])

Actually:

IIf(IsNull([YourFieldNameHere]),"",Left([YourFieldNameHere], 3) & Mid(7, [YourFieldNameHere]))

 

[raskew]

You've got a monster here.
Does 'JFK' alway appear in positions 4, 5, & 6?
Is 'JFK' always three characters long"

Suggest you go back and take a look at how your procedure
is creating that line of code?

And please, I don't mean to offend you.

Bob