Follow Up Question to MVP Reynolds on Splitting Fields

[Guest]

Brendan-
A new user of Access, I posted the question on 11/11/04 of how to split my
column of customer names into two columns.

I have studied your suggested query extensively and cannot determine why I
keep getting an invalid syntax error. You suggested I use:

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

which I modified to the following, to reflect my table name (bval04), and
field names (customername, lastname, firstname):

SET bval04.LastName = left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

Thanks in advance for whatever further direction you might offer. I reviewed
the information at your web site (impressive!), and also tried that email
address, but didn't find the answer - or one that I understood.

 

[Ken Snell [MVP]]

Post the full SQL statement that you tried to use.

 

[Brendan Reynolds]

UPDATE bval04 < -- did you leave out this bit?

SET bval04.LastName = left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

yes, I believe I have included the "update bval04" phrase.The (design view)
fields of my update query are as follows:

Field: customername
Table: bval04

Update to: UPDATE bval04 SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1), bval04.FirstName =
Mid$([Customername],InStr(1,[Customername],",")+2);

Criteria: (none)

The error that is returned suggests that the expression either contains
invalid syntax, or text data needs to be in quotes.

 

[Brendan Reynolds]

What I posted was a complete SQL statement, not an expression to go into the
'Update' row in query design view. Here's the original SQL statement
modified to use your table and field names ...

UPDATE bval04 SET LastName =
Left$([CustomerName],InStr(1,[CustomerName],",")-1), FirstName =
Mid$([CustomerName],InStr(1,[CustomerName],",")+2);

What you need to do is ...

Copy the above SQL statement to your clipboard.
Open your query in design view.
Select 'SQL View' from the View menu.
The entire SQL statement should be selected. Make sure it stays that way.
Paste the SQL statement from the clipboard.
Save the query.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

yes, I believe I have included the "update bval04" phrase.The (design
view)
fields of my update query are as follows:

Field: customername
Table: bval04

Update to: UPDATE bval04 SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1), bval04.FirstName =
Mid$([Customername],InStr(1,[Customername],",")+2);

Criteria: (none)

The error that is returned suggests that the expression either contains
invalid syntax, or text data needs to be in quotes.

Brendan-
A new user of Access, I posted the question on 11/11/04 of how to split
my
column of customer names into two columns.

I have studied your suggested query extensively and cannot determine why
I
keep getting an invalid syntax error. You suggested I use:

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

which I modified to the following, to reflect my table name (bval04), and
field names (customername, lastname, firstname):

SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

Thanks in advance for whatever further direction you might offer. I
reviewed
the information at your web site (impressive!), and also tried that email
address, but didn't find the answer - or one that I understood.

 

[Guest]

Following these instructions there are no longer syntax errors,but "view" on
the update query results returns the lastname, firstname column empty.

In looking at the table data, I notice the records are Smith, John (with a
space after the comma). I suspect I should have noticed this detail at the
beginning. Do I need to add the space to your SQL statement?

What I posted was a complete SQL statement, not an expression to go into the
'Update' row in query design view. Here's the original SQL statement
modified to use your table and field names ...

UPDATE bval04 SET LastName =
Left$([CustomerName],InStr(1,[CustomerName],",")-1), FirstName =
Mid$([CustomerName],InStr(1,[CustomerName],",")+2);

What you need to do is ...

Copy the above SQL statement to your clipboard.
Open your query in design view.
Select 'SQL View' from the View menu.
The entire SQL statement should be selected. Make sure it stays that way.
Paste the SQL statement from the clipboard.
Save the query.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

yes, I believe I have included the "update bval04" phrase.The (design
view)
fields of my update query are as follows:

Field: customername
Table: bval04

Update to: UPDATE bval04 SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1), bval04.FirstName =
Mid$([Customername],InStr(1,[Customername],",")+2);

Criteria: (none)

The error that is returned suggests that the expression either contains
invalid syntax, or text data needs to be in quotes.

Brendan-
A new user of Access, I posted the question on 11/11/04 of how to split
my
column of customer names into two columns.

I have studied your suggested query extensively and cannot determine why
I
keep getting an invalid syntax error. You suggested I use:

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

which I modified to the following, to reflect my table name (bval04), and
field names (customername, lastname, firstname):

SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

Thanks in advance for whatever further direction you might offer. I
reviewed
the information at your web site (impressive!), and also tried that email
address, but didn't find the answer - or one that I understood.

 

[Brendan Reynolds]

No, the +2 at the end of the statement takes the space into account. It's
just that viewing an update query in datasheet view does not update the
data. You can run the query by clicking the toolbar icon with the
exclamation point on it, or by choosing Run from the Query menu.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Following these instructions there are no longer syntax errors,but "view"
on
the update query results returns the lastname, firstname column empty.

In looking at the table data, I notice the records are Smith, John (with a
space after the comma). I suspect I should have noticed this detail at the
beginning. Do I need to add the space to your SQL statement?

What I posted was a complete SQL statement, not an expression to go into
the
'Update' row in query design view. Here's the original SQL statement
modified to use your table and field names ...

UPDATE bval04 SET LastName =
Left$([CustomerName],InStr(1,[CustomerName],",")-1), FirstName =
Mid$([CustomerName],InStr(1,[CustomerName],",")+2);

What you need to do is ...

Copy the above SQL statement to your clipboard.
Open your query in design view.
Select 'SQL View' from the View menu.
The entire SQL statement should be selected. Make sure it stays that way.
Paste the SQL statement from the clipboard.
Save the query.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

yes, I believe I have included the "update bval04" phrase.The (design
view)
fields of my update query are as follows:

Field: customername
Table: bval04

Update to: UPDATE bval04 SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1), bval04.FirstName =
Mid$([Customername],InStr(1,[Customername],",")+2);

Criteria: (none)

The error that is returned suggests that the expression either contains
invalid syntax, or text data needs to be in quotes.


:

Brendan-
A new user of Access, I posted the question on 11/11/04 of how to
split
my
column of customer names into two columns.

I have studied your suggested query extensively and cannot determine
why
I
keep getting an invalid syntax error. You suggested I use:

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

which I modified to the following, to reflect my table name (bval04),
and
field names (customername, lastname, firstname):

SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

Thanks in advance for whatever further direction you might offer. I
reviewed
the information at your web site (impressive!), and also tried that
email
address, but didn't find the answer - or one that I understood.

 

[Guest]

IT WORKED! THIS WAS JUST AMAZING!

It remains incredible to me that experts such as yourself donate your
knowledge in these forumns. Thank you, very much. I can see my afternoon
needs to be spent rereading the chapters on queries and SQL.

No, the +2 at the end of the statement takes the space into account. It's
just that viewing an update query in datasheet view does not update the
data. You can run the query by clicking the toolbar icon with the
exclamation point on it, or by choosing Run from the Query menu.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Following these instructions there are no longer syntax errors,but "view"
on
the update query results returns the lastname, firstname column empty.

In looking at the table data, I notice the records are Smith, John (with a
space after the comma). I suspect I should have noticed this detail at the
beginning. Do I need to add the space to your SQL statement?

What I posted was a complete SQL statement, not an expression to go into
the
'Update' row in query design view. Here's the original SQL statement
modified to use your table and field names ...

UPDATE bval04 SET LastName =
Left$([CustomerName],InStr(1,[CustomerName],",")-1), FirstName =
Mid$([CustomerName],InStr(1,[CustomerName],",")+2);

What you need to do is ...

Copy the above SQL statement to your clipboard.
Open your query in design view.
Select 'SQL View' from the View menu.
The entire SQL statement should be selected. Make sure it stays that way.
Paste the SQL statement from the clipboard.
Save the query.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


yes, I believe I have included the "update bval04" phrase.The (design
view)
fields of my update query are as follows:

Field: customername
Table: bval04

Update to: UPDATE bval04 SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1), bval04.FirstName =
Mid$([Customername],InStr(1,[Customername],",")+2);

Criteria: (none)

The error that is returned suggests that the expression either contains
invalid syntax, or text data needs to be in quotes.


:

Brendan-
A new user of Access, I posted the question on 11/11/04 of how to
split
my
column of customer names into two columns.

I have studied your suggested query extensively and cannot determine
why
I
keep getting an invalid syntax error. You suggested I use:

UPDATE TheTable SET TheTable.LastName =
Left$([FullName],InStr(1,[FullName],",")-1), TheTable.FirstName =
Mid$([FullName],InStr(1,[FullName],",")+2);

which I modified to the following, to reflect my table name (bval04),
and
field names (customername, lastname, firstname):

SET bval04.LastName =
left$([Customername],InStr(1,[Customername],",")-1),
bval04.FirstName = Mid$([Customername],InStr(1,[Customername],",")+2);

Thanks in advance for whatever further direction you might offer. I
reviewed
the information at your web site (impressive!), and also tried that
email
address, but didn't find the answer - or one that I understood.