Access Query- Return all text to the right of a character 1

[medeamedea]

I am using an Access query which includes new field which (will be) created by returning all the text to the right of a hyphen (from another field). I could use Right()but then I would I have to include an iif statement to look for every possible length which seems a a little sloppy. Is there a function I can use for this? I'm not a programmer, so please talk to me like I'm 5
Thank you!

 

[AnotherHiggins]

Yes - you can use Right()

No - you do not need an infinite number of Iifs, you just need to find where the hyphen is in the string.

Try this:
[tab][COLOR=blue white]NewName: Right([YourField], Len([YourField]) - InStr([YourField], "-"))[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[medeamedea]

Awesome, I will give this a go.

 

[dhookom]

If you only have a single hyphen in your [another field], you could use:

NewField: Mid([Another Field], Instr([Another Field],"-")+1)

If there is no hyphen, the entire [Another field] will be displayed. If there is no value, then the expression will return an error.

Duane
Hook'D on Access
MS Access MVP

 

[medeamedea]

Works like a charm. Thank you!!

 

[medeamedea]

Sorry, I didn't see the second suggestion. I used anotherhiggins' code, but I will save both for future use. Thanks again!!