Updating a Calculated Field to a Table

[Mark Drayton]

Hi,

I'm trying to update a table from an Update Query. In summary, I want the user to enter the full manager's name and an Expression to populate a separate field with their first name. I have managed to separate the first name, but am having problems in getting it to update the table.

This is the SQL query text:

SELECT MasterData.Home, MasterData.Email, MasterData.[Home Manager], Left([Home Manager],InStr([Home Manager]," ")) AS [Home Manager First]
FROM tblSelectID INNER JOIN MasterData ON tblSelectID.ID = MasterData.[Sun T1];

Query:
Leeds - john.smith@blah.com - John Smith - John (Expr)


Table:
Home - Email - Home Manager - Home Managers First


Have already tried to select Home Managers First as the update record.

Hope you can help

Many Thanks

Mark

 

[RichO]

Your SQL is for a select query, not an update query. You would need something like:

UPDATE MasterData SET MasterData.[Home Manager First] = Left$([Home Manager], InStr([Home Manager]," ") -1);

Adding a first name field when the first name already exists in another field is redundant. You can always use an expression in a form, query, or report to parse the first name when you need to use it. Why do you want to store it?

 

[Pat Hartman]

You're going to find out first hand why storing multiple fields in a single field is just plain wrong. Look what happens if you get a first name like "Mary Ann". To avoid having to parse it later, store the parts of a name separately from the beginning. Humans are far better at instinctively storing a name properly than software is at parsing it.