SQL Update statement help

[kalltim]

New Access user needs help.

I found an old post that pretty muched walked me through writing this code except that I get an error on the SQL update statement in the Where clause. In the update statement I am trying to update the last name and first name fields (which I split into two from the name field) for each current id. Can someone tell me the correct syntax to set the Where [campus ID] = currentidnumber. The code I have is not working.

Error Message-

Run time error '3075':

Syntax error (missing operator) in query expression '[Campus ID] = &rs![Campus ID]'.


Thanks for any help.

Timo



Code:

Option Compare Database
Option Explicit

Public Sub UpdateNameRecord()
Dim db As Database
Dim rs As Recordset
Dim Lastname As String
Dim FirstnamePlusInitial As String
Dim Firstname As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [CNSM Students]")
'Cycle through each record and extract the information you want to use.
Do While Not rs.EOF
'Find the location of the "," and extract the details to the left of it
Lastname = Left(rs![Name], InStr(rs![Name], ",") - 1)
'Find the location of the "," and extract the details to the right of it
FirstnamePlusInitial = Mid(rs![Name], InStr(rs![Name], ",") + 1)
'Find the location of the " " and extract the details to the right of it
Firstname = Left(FirstnamePlusInitial, InStr(FirstnamePlusInitial, " ") - 1)
'Run an update query
DoCmd.RunSQL "UPDATE [CNSM Students] SET [Last Name] = '" & Lastname & "', [First Name] = '" & Firstname & "' Where [Campus ID] = &rs![Campus ID]"

'Complete the loop and close the recordset
Loop
rs.Close

End Sub

[jzwp11]

You are opening a recordset based on the same table for which you are running the update query. You can do the edit directly in the recordset if you want (no need for the update query).

Technically speaking you can accomplish the same thing with a straight update query--no need for any code.

What do the names look like? Can you provide an example? Do all have a middle initial?

'Find the location of the " " and extract the details to the right of it
Firstname = Left(FirstnamePlusInitial, InStr(FirstnamePlusInitial, " ") - 1)

BTW you say to extract details to the right but you use the left function.

[kalltim]

Thanks jzwp11 for your response. I will be running this code twice a year and therefore wanted the code written out. This will also help someone who might replace me in my position who needs to maintain the database. Any thoughts on fixing the update statement?

Exampme of a name: Johnson, Michael A

[jzwp11]

What do you want to do with the middle initial? You did not have a corresponding middle initial field.

Why would you want to do this more than once? You would essentially correct the existing records, but you would make sure new records have the name broken out into the fields.

I would guess that once you separated out the names, you would get rid of the name field altogether.

[jzwp11]

I typically do the separation in a SELECT query to make sure I get it right. So yours would look something like this go separate out the first name, last name and middle initial

SELECT [name], Left([Name], InStr([Name], ",") - 1) AS LastName, IIF(instrrev([name]," ")= len([name])-1,trim(mid([name],instr([name],",")+2,instrrev([Name]," ")-1-instr([Name],","))), trim(right([name],instrrev([name]," ")))) AS FirstName, IIF(instrrev([name]," ")= len([name])-1,Right([name],1),null) AS MI
FROM [CNSM Students];


Now we can create an update query

UPDATE [CNSM Students] SET [CNSM Students].[Last Name] = Left([Name],InStr([Name],",")-1), [CNSM Students].[First Name] = IIf(InStrRev([name]," ")=Len([name])-1,Trim(Mid([name],InStr([name],",")+2,InStrRev([Name]," ")-1-InStr([Name],","))),Trim(Right([name],InStrRev([name]," ")))), [CNSM Students].MI = IIf(InStrRev([name]," ")=Len([name])-1,Right([name],1),Null);

[kalltim]

Changing the update statement to the following fixed the syntax problem with my code.

DoCmd.RunSQL "UPDATE [CNSM Students] SET [Last Name] = '" & Lastname & "', [First Name] = '" & Firstname & "' Where [Campus ID] = ' " & rs![Campus ID] & "'"

Played around with Query and learned how to do some basic appending, updating, etc.

Query doesn't seem to work all the time like when I was trying to do an update inner join, but I can see how it is useful for simpler requests.

The only other situation I ran into was when I tried to seperate first and last names was when there was an imbedded approstrophe in the name like: O'brian, Mally. The extra approsophe hung up my concatenation syntax: '" & Lastname & "'. Probably a work around here, but didn't have time to play with it yet.

Thanks for your help.

Tim

[jzwp11]

Query doesn't seem to work all the time like when I was trying to do an update inner join, but I can see how it is useful for simpler requests.

You might have to use the query with the inner join to identify the records and nest that in the update query

Something like this

UPDATE tablename SET fieldname=x WHERE IDfield in (Select IDfield FROM..INNER JOIN...)

The only other situation I ran into was when I tried to seperate first and last names was when there was an imbedded approstrophe in the name like: O'brian, Mally. The extra approsophe hung up my concatenation syntax: '" & Lastname & "'. Probably a work around here, but didn't have time to play with it yet.

This site has a function called adhHandleQuotes that should help.