I am trying to convert this SELECT query into an UPDATE query...

SELECT Left([BFirstName2],InStr([BFirstName2]," ")) AS BFirstName,
Mid([BFirstName2],InStr([BFirstName2]," ")+1) AS BLastName
FROM [77777 Show];

I tried simply replacing SELECT with UPDATE which is what I thought was
needed to be done. What am I missing?

What I am trying to do is UPDATE the data in table 77777 with the results of
the SELECT query.

Help!

No ... you need the SET Clause in the UPDATE Query like

UPDATE [MyTable]
SET [MyField] = [Myfield] * 2

(meaning doubling the value of [MyField])

--
HTH
Van T. Dinh
MVP (Access)



"SinCity" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to convert this SELECT query into an UPDATE query...
>
> SELECT Left([BFirstName2],InStr([BFirstName2]," ")) AS BFirstName,
> Mid([BFirstName2],InStr([BFirstName2]," ")+1) AS BLastName
> FROM [77777 Show];
>
> I tried simply replacing SELECT with UPDATE which is what I thought was
> needed to be done. What am I missing?
>
> What I am trying to do is UPDATE the data in table 77777 with the results
> of the SELECT query.
>
> Help!
>

UPDATE [77777] INNER JOIN [77777 Show]
ON [77777].ID = [77777 Show].ID
SET BFirstName = Left([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," "))
BLastName = Mid([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," ")+1)

This assumes that the Primary Key for both tables is named ID. You'll have
to fuss with the ON clause if that's not the case.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"SinCity" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to convert this SELECT query into an UPDATE query...
>
> SELECT Left([BFirstName2],InStr([BFirstName2]," ")) AS BFirstName,
> Mid([BFirstName2],InStr([BFirstName2]," ")+1) AS BLastName
> FROM [77777 Show];
>
> I tried simply replacing SELECT with UPDATE which is what I thought was
> needed to be done. What am I missing?
>
> What I am trying to do is UPDATE the data in table 77777 with the results
> of the SELECT query.
>
> Help!
>

news:(E-Mail Removed)...
> UPDATE [77777] INNER JOIN [77777 Show]
> ON [77777].ID = [77777 Show].ID
> SET BFirstName = Left([77777 Show].[BFirstName2],InStr([77777
> Show].[BFirstName2]," "))
> BLastName = Mid([77777 Show].[BFirstName2],InStr([77777
> Show].[BFirstName2]," ")+1)
>
> This assumes that the Primary Key for both tables is named ID. You'll have
> to fuss with the ON clause if that's not the case.

Thanks so much Doug. I tried your suggestion but got the following error...

"Syntax Error in JOIN Operation".

Here is my exact code. Note that I changed the name of the table from "7777
Show" to "73106".

UPDATE [73106] INNER JOIN [73106]
ON [73106].ID = [73106].ID
SET BFirstName = Left([73106].[BFirstName2],InStr([73106].[BFirstName2],"
"))
BLastName = Mid([73106].[BFirstName2],InStr([73106].[BFirstName2]," ")+1)

Any idea what is wrong?

Since you have only 1 Table, you don't need the join. try:

========
UPDATE [73106]
SET BFirstName = Left( [BFirstName2], InStr([BFirstName2]," ") ),
BLastName = Mid( [BFirstName2], InStr([BFirstName2]," ")+1 )
========


--
HTH
Van T. Dinh
MVP (Access)



"SinCity" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks so much Doug. I tried your suggestion but got the following
> error...
>
> "Syntax Error in JOIN Operation".
>
> Here is my exact code. Note that I changed the name of the table from
> "7777 Show" to "73106".
>
> UPDATE [73106] INNER JOIN [73106]
> ON [73106].ID = [73106].ID
> SET BFirstName = Left([73106].[BFirstName2],InStr([73106].[BFirstName2],"
> "))
> BLastName = Mid([73106].[BFirstName2],InStr([73106].[BFirstName2]," ")+1)
>
> Any idea what is wrong?
>