Re: Update field with contents of another field

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: Cant get Access to run a query with a time field
    ... Since the user enters an exact value, why isn't it saved to the ... Second, a date time value is a floating point value, ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Another variation
    ... >> Vanderghast, Access MVP ... > The documentation may need some maintenance. ... I couldn't locate the Jet4.0 SQL reference on ...
    (microsoft.public.access.queries)
  • Re: Display list of files in a folder on a hard drive in a form
    ... Dim str As String ... >> Vanderghast, Access MVP ... >>> then have it display all of the files located in the folder on a form. ...
    (microsoft.public.access.formscoding)
  • Re: lookup
    ... graphical capabilities of the designer. ... >> FROM myOriginalTable LEFT JOIN QuotesCodes ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Finding the end of a recordset
    ... >> That error is generally due to some invalid data in the record to be ... >> Vanderghast, Access MVP ... >> they hit delete, ...
    (microsoft.public.access.queries)