Split data in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a cell which is a DOB field. the format is Date/Time. The
data appears like this MM/DD/YYYY. I need to split it up in three fields,
Field 1=MM, Field 2=DD, and Field 3=YYYY. How would I do that?
 
I have a field in a cell which is a DOB field. the format is Date/Time. The
data appears like this MM/DD/YYYY. I need to split it up in three fields,
Field 1=MM, Field 2=DD, and Field 3=YYYY. How would I do that?
Click to expand...

If the field is a data time field and all you want to do is display
the month, day or year, all you need do is format the control.

Set the Control's Format property to
yyyy for the year,
mm for the 2 digit moth, or
dd for a 2 digit day.

There is really no good reason that I can think of to store 3
different parts of a date.

If you needed to get a list of birthdays for the current month, for
example, all you need do is use something like this in a query.

Select [FirstName] & " " & [LastName] as FullName, YourTable.[DOB]
from YourTable
Where Format(DOB],"mm") = Format(Date(),"mm")
 
I have a similar question as it refers to a cell with Names. The names are
stored as last, first. I want to split this into two columns in a query to
show last and first seperately. Any suggestions?
 
As mentioned very often in these groups (please search before posting)...

You would want to correct your database structure to include a FIRTSNAME and
LASTNAME field, then build an update query to take everything to the left of
the comma and insert it in the LASTNAME field. Take everything to the right
and insert in the FIRSTNAME field. Then delete your combined field.

You would use INSTR and MID to pull out the parts of the name. Read the
many many previous posts if you need details.
 
Note, though, that it's not as simple as Rick makes it sounds.

How are you going to split Elly May Clampett? How are you going to split
Ludwig von Beethoven? What are you going to do with people with only one
name, such as Charo?
 
Doug:

I thought of that, but she indicates they are stores as LAST, FIRST.

That should not be an issue in this case.

--
Rick B



Douglas J Steele said:
Note, though, that it's not as simple as Rick makes it sounds.

How are you going to split Elly May Clampett? How are you going to split
Ludwig von Beethoven? What are you going to do with people with only one
name, such as Charo?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rick B said:
As mentioned very often in these groups (please search before posting)...

You would want to correct your database structure to include a FIRTSNAME and
LASTNAME field, then build an update query to take everything to the
Click to expand...
left
Click to expand...
 
Back
Top