Parameterized update in SQL(Microsoft Access)

Question

I'm trying to update the state capital from table STATES with a city from table CITIES. I want both , the state name and city name to be parameterized.

have this code

UPDATE STATES
SET State_capital=DLookUp("ID","CITIES","City='Los Angeles'")
WHERE State_name=[Enter State Name:];

The problem is that the City name is not parameterized in DLookUp and Access does not accept this form of the update :

UPDATE STATES
SET State_capital=(SELECT ID FROM CITIES WHERE City=[Insert city here])
WHERE State_name=[Enter State Name:];

What is the solution?Thank you !

http://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update — Sathish, Oct 18 '14 at 10:12

score 3 · Answer 1 · answered Oct 18 '14 at 11:48

This query works for me in Access 2010:

PARAMETERS [Enter City name:] Text(255), [Enter State name:] Text(255);
UPDATE STATES SET 
State_capital=DLookup("ID","CITIES","City='" & Replace([Enter City name:],"'","''") & "'")
WHERE State_name=[Enter State name:]

Parameterized update in SQL(Microsoft Access)

1 Answers1