Update Query to 'strip' - not working in Office 2007??

[Doc]

Hi there,
Have a large SQL file with a field (Name) which has the first and last
name separated by a ~ . I want to 'strip' out the first and last name
into their own fields.

I went into the TABLE and 'added' fields of 'FirstName' and 'LastName'
and also Organization (about 100 entries are not names but Companies)
and then used a previously successful query (as follows):

It is an 'update' query - or so it seems from what I understand about
2007 Access.

Under FirstName in the UPDATE TO cell enter the formula
TRIM(Left([tblName].[FieldName],Instr(1,[tblName].[FieldName],"~")))

UnderLastName enter the formula
TRIM(MID([tblName].[FieldName],1+Instr(1,[tblName].[FieldName],"~")))


Under the FieldName in the criteria enter Like "*~*"

Select Query: Run from the menu

When I run it, a PROMPT asks for a 'parameter' entry for the
tblname:fieldname and I'm not sure how to answer that - I believe that,
even though this worked in 2003 (no longer available) I'M DOING
something wrong here.

Appreciate any and all help. Thank you.

Doc

 

[KARL DEWEY]

Have a large SQL file with a field (Name) which has the first and last
name separated by a ~ .
What is the actual name of the field? Above it appears to be called "Name"
but below in the query you are using "fieldname" which it must not be.

You gotta use exact names. That is why it does not know the field label you
used in the query.

 

[Doc]

The actual name of the 'field' is NAME.
The table name is ACTUAL.

So if I'm following you correctly, I need to make the [tblName] to be
[actual] ?
And I'd make the [FieldName] to be [Name] ?

Will try that, many thanks

********************
It is an 'update' query - or so it seems from what I understand about
2007 Access.

Under FirstName in the UPDATE TO cell enter the formula
TRIM(Left([tblName].[FieldName],Instr(1,[tblName].[FieldName],"~")))

UnderLastName enter the formula
TRIM(MID([tblName].[FieldName],1+Instr(1,[tblName].[FieldName],"~")))


Under the FieldName in the criteria enter Like "*~*"


KARL DEWEY typed this:

 

[John W. Vinson]

The actual name of the 'field' is NAME.
The table name is ACTUAL.

NAME is a reserved word. A Form has a Name property, a field has a Name
property, a table has a Name property! Access can and will get confused.

If you cannot change the Name of Name to a Name that is a unique Name which
won't be confused with some other Name, then you must always use [square
brackets]: if you say [Name] instead of just Name, Access will have a somewhat
better chance of figuring out what you mean.


John W. Vinson [MVP]

 

[Doc]

John W. Vinson typed this:

The actual name of the 'field' is NAME. The table name is ACTUAL.

NAME is a reserved word. A Form has a Name property, a field has a
Name property, a table has a Name property! Access can and will get
confused.

If you cannot change the Name of Name to a Name that is a unique Name
which won't be confused with some other Name, then you must always
use [square brackets]: if you say [Name] instead of just Name, Access
will have a somewhat better chance of figuring out what you mean.


John W. Vinson [MVP]

IT WORKED, I thank you all so much - I know many things in different
arenas but DATABASE and SQL are most difficult for me. Thank you again!

Doc