return all after specific character. left, right, mid???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scubadaisy - 24 Apr 2008 19:35 GMT

I'm having a complete brain fart and cant find it in help. what is the
formula to return a value after a specific character. the most common
example is when you have a last, first name in one field and want to split it
out.

Reply to this Message

Jerry Whittle - 24 Apr 2008 19:48 GMT

Debug.Print Mid("Whittle, Jerry",Instr("Whittle, Jerry", ",")+2)

Jerry

That's assuming that it's the comma that you are looking for.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I'm having a complete brain fart and cant find it in help. what is the
> formula to return a value after a specific character. the most common
> example is when you have a last, first name in one field and want to split it
> out.

Reply to this Message

Scubadaisy - 24 Apr 2008 19:55 GMT

InStr - that's what I was looking for!!! :-) Thank you - blond moment over.
Have a great day

> Debug.Print Mid("Whittle, Jerry",Instr("Whittle, Jerry", ",")+2)
>
[quoted text clipped - 6 lines]
> > example is when you have a last, first name in one field and want to split it
> > out.

Reply to this Message

fredg - 24 Apr 2008 19:51 GMT

So what is the specific character?
Is it a space? Or is it a comma and space, or is it .....?

= Mid([fieldName],InStr([FieldName],", ")+2)

will find "John" if the field contains "Smith, John"

=Left([FieldName],InStr([FieldName],", ")-1)
will find "Smith".

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Scubadaisy - 24 Apr 2008 20:10 GMT

Ok - one more question.

my field data is: "1/15/2008 5:29:38 PM"

My end goal is just to pull the date away from the time. I am unable to
re-format the time date field to just display the date nor can I put it in a
standardized mm/dd/yyyy hh:mm:ss format. it drops the leading zeros
automatically.

So, I have InStr characters to look for in the "/" and even the spaces so
I've decided to break it down to basics and pull out each the month, day and
year into 3 new fields. The month is easy, but what do you recomend for the
day and year???

> > I'm having a complete brain fart and cant find it in help. what is the
> > formula to return a value after a specific character. the most common
[quoted text clipped - 10 lines]
> =Left([FieldName],InStr([FieldName],", ")-1)
> will find "Smith".

Reply to this Message

John Spencer - 24 Apr 2008 20:42 GMT

IIF(Isdate([SomeField]),DateValue([SomeField]),Null)

If you want the time
IIF(Isdate([SomeField]),TimeValue([SomeField]),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Ok - one more question.
>
[quoted text clipped - 24 lines]
>> =Left([FieldName],InStr([FieldName],", ")-1)
>> will find "Smith".

Reply to this Message

Marshall Barton - 24 Apr 2008 20:41 GMT

>I'm having a complete brain fart and cant find it in help. what is the
>formula to return a value after a specific character. the most common
>example is when you have a last, first name in one field and want to split it
>out.

The InStr function is also very useful for that kind of
thing.

Signature

Marsh
MVP [MS Access]

Reply to this Message