Re: Update field with contents of another field

You were right, I did have a typo. Current_Veh_Location should have been Current_Veh_Loc

But I corrected that... and I still get the same error message. I'm so close!!

I'm Googling for answers, but everything I find points to typo errors. I've looked very closely at the query and find none.

Any other recommendations would be greatly appreciated.




"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message news:e$nIv2fAJHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
Probably a typo: the field is not recognized.

Vanderghast, Access MVP

"JohnB" <jbrigan@xxxxxxxxx> wrote in message news:%230KoPsfAJHA.1016@xxxxxxxxxxxxxxxxxxxxxxx
I have a followup question:
I didn't explain the table and field names correctly, but I could figure it out from what you wrote. This is your query with the corrected table/field names:

UPDATE Products INNER JOIN All_Locations
ON Products.ProductStock=All_Locations.Stock_Number
SET Products.Actual_Location = All_Locations.Current_Veh_Location
WHERE Products.Actual_Location IS NULL;

But my problem is; I am getting prompted for information when I run the query. I get a popup window titled "Enter Parameter Value".
With the input box labeled "All_Locations.Current_Veh_Location"

What is causing that prompt?

TIA



"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message news:exF9h1WAJHA.4148@xxxxxxxxxxxxxxxxxxxxxxx
UPDATE tableB INNER JOIN tableA
ON tableB.stockNumber = tableA.stockNumber
SET tableB.Location= tableA.Location
WHERE tableB.Location IS NULL


Sure, just in case it does not do what you want, preferable to experiment AFTER having made a backup. An UPDATE can make extensive damage to your data, if not properly done.

Vanderghast, Access MVP



"JohnB" <jbrigan@xxxxxxxxx> wrote in message news:e47EVjWAJHA.4588@xxxxxxxxxxxxxxxxxxxxxxx
I now know how to update a field, with text such as "record found", based on the contents of another field.

I use something like this:
update tableb,tablea set tableb.blankfield = "found"
where tableb.stocknumber = tablea.stocknumber

But I need to do something a little different, and I can't figure out how to do this:

I have a file with a link to a table in another file. And just as before, I need to read the StockNumber field in one table and find matching records in the linked table. When I find a match I need to update the contents of Table-B.Location with the contents of Table-A.location

I tried, in place of "found", TableName.FieldName. but I get a message saying Syntax error, missing operator.....
It's probably something simple, but since I've never done this before, I don't know what the correct syntax would be.

TIA






.

Relevant Pages

  • Re: Update field with contents of another field
    ... I have a followup question: ... I am getting prompted for information when I run the query. ... What is causing that prompt? ... but I get a message saying Syntax error, ...
    (microsoft.public.access.queries)
  • Re: Correlation Tables -- Setup
    ... a typo to the right of E6. ... The solution to your query no doubt lies with the INDIRECT function ... doesn't the Rank correlation require two different ... one dependent on the other? ...
    (microsoft.public.excel.worksheet.functions)
  • Limit results to StrComp that produce errors
    ... I am trying to create a case sensitive query that identifies when users have ... that have the error condition. ... Only 5 of these lines have a TO typo. ... Is there any way to limit my results so I only see the error rows? ...
    (microsoft.public.access.queries)
  • Re: Queries and OO
    ... > code with typos in unfamiliar syntax is too much of a burden. ... query is supposed to do. ... table vs. Movie implemented as an object? ...
    (comp.object)
  • RE: Need to chg queried table
    ... Fixed my typo and just get the same error but with a space in it. ... Syntax error in query expression ... >> Dim strSQL As String ... >> Set dbsCurrent = CurrentDb ...
    (microsoft.public.access.formscoding)