Translate Excel Formula into Access Formula

[316854]

I'm lost.

How can I get this:

1234 N Main Ave Los Angeles CA 90001-1234

to show up in the field next to this:

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

inside the form?

I was able to do it inside a query, but how do I get that information inside the form?

Thanks

[June7]

If query is in the form RecordSource, can bind a textbox to the constructed field.

[ssanfu]

See the example dB in Post#15

[316854]

I understand now.

I was trying to have the record source for the form be from a table and a query.

One more question. In datasheet view, I'm copying a list of addresses. How can I not copy the name of the field with the chunk of addresses I've highlighted?

Thanks

[June7]

You want to copy/paste from Access table to Excel? Sorry, the field name tags along. No control over that. Copy/paste to Word and the table name also tags along.

[316854]

How can I get this:

John Smith

to show up in the field next to this:

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

Thanks

[June7]

You said you already concatenated those fields with calc in query. Bind form to the query and bind textbox to that calculated field.

Or if you bind directly to table, do the calculation in textbox ControlSource property.

[316854]

What is the formula?

[June7]

I am confused. In post 16 you say "I was able to do it inside a query,..."

Just what did you do inside query?

[316854]

Address: Trim(Mid([Location1a],InStr(Nz([Location1a],""),"@")+1))

I want to keep what's behind the @ this time.

Thanks

[June7]

If you just want to concatenate fields:

[field1] & " " & [field2]

[316854]

No, concatenate will not work.

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

I want "John Smith" to be extracted and put in another field.

Thanks

[316854]

John Smith @ 1234 N Main Ave Los Angeles CA 90001-1234

I want only "John Smith" to be copied to another field.

I want everything before the @ sign to be listed in another field.

Thanks

[June7]

So you want to parse again. More string manipulation, similar to the code you have already been given. Might want to get really familiar with string manipulation functions. http://msdn.microsoft.com/en-us/library/dd789093.aspx

Left([fieldname], InStr([fieldname], "@") - 1)

[316854]

Thank you!

Thank you for the link as well.

I'm not terrible with excel formulas, but I'm not familiar with access functions yet.

Thanks