Why am I asked for "Enter parameter value" when running update query in MS Access?

Question

I've been struggling with my query and don't know what is wrong with it. I am not an advanced SQL user, but I have some understanding of SQL.

I have two tables and I am trying to update one field with the field from the other table. They do not have a unique identifier in each, so they need to be matched with three fields (HoleID, From, To), especially because table LEAPFROG_Lithology has more records that do not have the same From/To than in the Lithology table.

I have looked for questions asked on similar subject, and tried different variant of my query, but none work and it always asks me to "Enter parameter value". I tried with an inner joint like in this one and one, but it did not work.

Here is what I tried:

Version 1

Update LEAPFROG_Lithology 
SET LEAPFROG_Lithology.Primary_litho = (
SELECT Lithology.Primary_litho 
FROM Lithology
RIGHT JOIN LEAPFROG_Lithology ON (
    Lithology.[HoleID] = LEAPFROG_Lithology.[HoleID] and 
    Lithology.[From_m] = LEAPFROG_Lithology.[From_m] and 
    Lithology.[To_m] = LEAPFROG_Lithology.[To_m])) 
Where LEAPFROG_Lithology.Primary_litho is null  ;

Version 2 (tried with an INNER JOIN as well)

Update LEAPFROG_Lithology
LEFT JOIN Lithology ON (
    LEAPFROG_Lithology.[HoleID] = Lithology.[HoleID] and 
    LEAPFROG_Lithology.[From_m] = Lithology.[From_m] and 
    LEAPFROG_Lithology.[To_m] = Lithology.[To_m]) 
SET LEAPFROG_Lithology.Primary_litho = Lithology.Primary_litho 
Where LEAPFROG_Lithology.Primary_litho is null  ;

I tried with [ ] everywhere, with none. It doesn't make a difference.

Can someone tell me what am I doing wrong? Thanks!

You usually get that message when you've typoed a column or table name. Does it give you the name of the "parameter" it is looking for? — ArcherBird, Oct 29 '18 at 18:34
Could you post screenshots of your `LEAPFROG_Lithology` and `Lithology` tables/queries? — Lee Mac, Oct 29 '18 at 18:41
@ArcherBird, it asks me for only one parameter, 'Lithology.Primary_litho'. — Liix, Oct 30 '18 at 19:12

score 0 · Answer 1 · answered Oct 29 '18 at 18:49

Make sure both your tables can form a relationship before using JOIN, you need to make sure your PRIMARY KEY in one table corresponds to the FOREIGN KEY in the other table to join them.

Access lets you use a design view to create queries which can be easy if you're not fluent with SQL. Once you design it visually you can switch to SQL view to find the code.

score 0 · Answer 2 · answered Oct 29 '18 at 19:25

Please verify that the names of the tables and fields are correct. If so, the second query should work.

But I suggest to use a slightly different bracketing, so you can display the graphical design view of the query. Also, there's no need to use a LEFT JOIN, because for records that have no Primary_litho and don't have a matching Lithology, the NULL value would be updated with a NULL value. So this should work without any problem:

UPDATE LEAPFROG_Lithology
INNER JOIN Lithology ON 
   (LEAPFROG_Lithology.[HoleID] = Lithology.[HoleID]) AND
   (LEAPFROG_Lithology.[From_m] = Lithology.[From_m]) AND
   (LEAPFROG_Lithology.[To_m] = Lithology.[To_m])
SET LEAPFROG_Lithology.Primary_litho = Lithology.Primary_litho 
WHERE LEAPFROG_Lithology.Primary_litho Is Null;

Why am I asked for "Enter parameter value" when running update query in MS Access?

2 Answers2