I am getting data passed to me in one field that needs to be broken apart. The data is first name, middle name, last name and birthdate but its in one field that looks like this:

john/mark/doe/06121972

Is it possible to break apart this field into 4 separate fields?

The Split() function will break it up in an array for you if you want.

as long as there are always 3 / characters (ie 4 sections) then you can write a simple function to strip the text in each section

function section(s as string, slice as integer) as string
ie find slice 1,2,3,4 in string s

dim slashpos1 as integer
dim slashpos2 as integer
dim slashpos3 as integer

slashpos1 = instr(s,1,"/") 'position of first slash
slashpos2 = instr(s,slashpos1+1,"/") 'position of second slash
slashpos3 = instr(s,slashpos2+1,"/") 'position of third slash

select case slice
case 1: 'retrieve left string to first slash
section = left(s,slashpos1-1)

case 2: 'retrieve first slash to secondslash
section = mid(s,slashpos1+1,slashpos2-1 -slashpos1 )

case 3: 'retrieve second slash to thirdslash
section = mid(s,slashpos2+1,slashpos3-1 -slashpos2 )

case 4: 'retrieve endof string after third slash
section = mid(s,slashpos3+1)
end select
end function

not checked it, but it will be close! Its slightly inefficient as you are evaluating all the slash positions, when you dont necessarily need them all, but this is minor. Also it doesnt deal with strings NOT having 3 slashes.

[edit - is split in a version of access after A97 - it sounds useful]

I think you would have to do it using the split function and recordsets in VBA.

Thanks for the hints. I will write the funciton and see what happens.