Append Part of a Text Field to another field

[matthewnsarah07]

I have the form, frmAddStaff which contains the field - this is in the format [EMAIL="User.Name@Email.com"]User.Name@Email.com.

The first part of the address is the Username which I want to add to a further table - tbltm. This contains the field [Username]

How can I set up a query that will append the from the frmAddStaff to tbltm and in the process remove the [EMAIL="'@Email.com'"]'@Email.com'

Thanks

 

[DCrake]

UserName:IIF(InStr([EmailAdr],"@")> 1,Left([EmailAdr],Instr([EmailAdr],"@")-1),"")

This first checks if there is an @ in the phrase, and if so it parses everything to the left of the position where the @ sign appears in the email address, else it returns ""

 

[jdraw]

Since your form is called "frmAddStaff", it follows you are going to ADD a record to your table. What other fields make up the table? Don't you want to add those as well?
Or, does the table exist and only the email is currently blank?

You will need some code in the After Update event of the email field on the form.
You would use Instr(Me.email,"@") to ensure that a "@" actually exists.
The Instr(Me.email,"@") will give you the position of the "@" in the field. This must not = 0(or the character wasn't found).

Then the string you want will be similar to this
Left(Me.email,Instr(me.email,"@")-1)
which says -- take the characters at the Left of Me.email for a length equal to the position of the "@" -1

Good luck.


Update: Looks like David answered while I was typing.

 

[matthewnsarah07]

Thank you both for your assistance, that has worked perfectly.

I had picked this code up but wasn't sure how it worked