Can I UPDATE a table with value from other table?

[NewComer]

I have a MS Access database with:

. a table RoomList with 5 columns: RoomNum, Daily, Weekend, Holiday, Special
with 1 row has RoomNum values of 102

. a table AssignedList with 6 columns: RNum, RDaily, RWeekend, RHoliday, Special, GConf with 1 row has ordered values are 102 $50.00 $60.00 $70.00 $100.00 '12345'

*) Except the GConf equal String, the other fields are same types

- I want to copy those 4 rates from table AssignedList where GConf = '12345' to table RoomList from same Room 102

Here is my codes:

strSQL = "UPDATE RoomList SET "
strSQL = strSQL & "(RoomList.Daily = AssignedList.RDaily, RoomList.Weekend = AssignedList.RWeekend, "
strSQL = strSQL & "RoomList.Holiday = AssignedList.RHoliday, RoomList.Special = AssignedList.RSpecial) "
strSQL = strSQL & "FROM RoomList INNER JOIN AssignedList ON RoomList.RoomNum = AssignedList.RNum "
strSQL = strSQL & "AND AssignedList.GConf = '12345' "
cmdMSA = New OleDbCommand(strSQL, dbConn2)
Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If

Catch ex As Exception
MessageBox.Show("Error Update RoomList from AssignedList = " & ex.Message)
End Try

cmdMSA.Dispose()

It has error: Syntax error (missing operator) in query expression 'AssignedList.RSpecial FROM RoomList INNER JOIN AssignedList ON RoomList.RoomNum = AssignedList.RNum'

Can someone correct the problem or if MS Access can't accept them, might we have other similiar codes?

 

[InertiaM]

1. Please use CODE tags when posting code - it makes it SO much easier to read.

2. Please ensure the error message you post relates to the code you post. In your case, your code says:-

AssignedList.RSpecial) FROM RoomList

but your error message says:-

AssignedList.RSpecial FROM RoomList

so the code cannot relate to the error message.

3. I dont use Access, but a quick search seems to suggest that the syntax is the other way round. I found this example:-

UPDATE Bot INNER JOIN Big ON Bot.PART = Big.PART
SET Bot.MFG = [Big].[MFG];

 

[NewComer]

Thanks for help

I will be carefull next time, your codes & link are working ... except that I have to replace the ; to ,

Thanks for help