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

  • Thread starter Thread starter Scubadaisy
  • Start date Start date
S

Scubadaisy

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.
 
Debug.Print Mid("Whittle, Jerry",Instr("Whittle, Jerry", ",")+2)

Jerry

That's assuming that it's the comma that you are looking for.
 
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.
Click to expand...

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".
 
InStr - that's what I was looking for!!! :-) Thank you - blond moment over.
Have a great day
 
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???

fredg said:
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.
Click to expand...

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".
Click to expand...
 
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.

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???

fredg said:
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.
Click to expand...
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".
Click to expand...
Click to expand...
 
Back
Top