String passing

[Blastbeats]

Hello Everyone, I am working on an access database and i need to take some code that comes on one field and add it to another, a quick example, i have the field description and it comes like 1234 description 1, the code is always separated by a space from the description, I pretty much need to take out the 1234 out of the whole thing and add it to another field alone?, what possibilities could i have that you know of?, thanks in advance

 

[vbaInet]

You will need the Left() and Instr() functions. See here for more info:

http://www.techonthenet.com/access/functions/

 

[Blastbeats]

Thanks a million this is exactly what i needed

 

[Blastbeats]

by the way do you know any way to substract 1 to the instr returned value inside the left function? im doing something like left ([Field],instr([field]," ")), except the instr value needs to have something like this ([Field],instr([field]," ")-1) but access won't let me do that

 

[vbaInet]

That's perfectly fine but if Instr() returns 0 then 0-1 will yield -1 which is an invalid value in the second parameter of the the Left() function. So you need to use that only when Instr() returns a value greater than 0.

 

[boblarson]

I think you need something more like this:

IIf(Instr(1, [Field], " ") > 0, Left([Field], Instr(1, [Field])-1), [Field])