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 !

stackoverflow.com/questions/16568461/…

Sathish
– Sathish

2014-10-18 10:12:47 +00:00
Commented Oct 18, 2014 at 10:12 — Sathish
– Sathish, Commented Oct 18, 2014 at 10:12

Gord Thompson · Accepted Answer · 2014-10-18 11:48:51Z

3

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:]

answered Oct 18, 2014 at 11:48

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parameterized update in SQL(Microsoft Access)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related