Re: Update Query
From: Dale Brown (dalebrown_at_gaitkeeper.com)
Date: 02/27/05
- Next message: SteveS: "Re: Problem with query expression"
- Previous message: Ken Snell [MVP]: "Re: Update Query"
- In reply to: Ken Snell [MVP]: "Re: Update Query"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 26 Feb 2005 22:30:39 -0600
That did it. Thanks
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:esiFPPIHFHA.3088@tk2msftngp13.phx.gbl...
> Oh sorry.. it's late here and I overlooked that the WHERE clause goes
> after the SET clause.
>
>
> update classinfo
> INNER JOIN activeshow ON ClassInfo.ShowID=ActiveShow.ShowID
> set classinfo.scoreid = DLookup("scoreid", "classinfoscore")
> WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
> forms!ClassFill!endnbr;
>
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Dale Brown" <dalebrown@gaitkeeper.com> wrote in message
> news:uNa$4MIHFHA.2616@tk2msftngp13.phx.gbl...
>> This is from a sql view. The offending word is the where. This happens
>> with yours and mine.
>>
>> "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>> news:%23isk2FIHFHA.2616@tk2msftngp13.phx.gbl...
>>> Are you trying to run this from the query window? or from code?
>>> Normally, when you get an syntax error in the SQL view, often the
>>> offending word in the erroneous clause will be highlighted (or the
>>> beginning of that clause will be highlighted).
>>>
>>> If you're trying to run this from code, where you're building the string
>>> via VBA steps, then post the code that you're using. Often, the problem
>>> in this case is that you are not adding spaces into the string as you
>>> build it (not uncommon error).
>>>
>>> Assuming that this is the SQL statement from the SQL view of the query,
>>> try this:
>>>
>>> update classinfo
>>> INNER JOIN activeshow ON ClassInfo.ShowID=ActiveShow.ShowID
>>> WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
>>> forms!ClassFill!endnbr
>>> set classinfo.scoreid = DLookup("scoreid", "classinfoscore");
>>>
>>> --
>>>
>>> Ken Snell
>>> <MS ACCESS MVP>
>>>
>>> "Dale Brown" <dalebrown@gaitkeeper.com> wrote in message
>>> news:OA5IXBIHFHA.3484@TK2MSFTNGP12.phx.gbl...
>>>> When I try to run the query I get an Syntax error in update statement
>>>> message. The activeshow table is a table that has one record with a
>>>> showid. The user sets the active show with a form like you thought.
>>>> Then everything that is done with classes is for only that show. The
>>>> inner join will keep me from getting any other classinfo records from
>>>> other shows.
>>>>
>>>>
>>>> "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>>> news:u97TL8HHFHA.2704@tk2msftngp13.phx.gbl...
>>>>> How does the joining to activeshow table tell you which show the user
>>>>> is working on? The query, as you've written it, would have no reason
>>>>> to join to activeshow table *unless* you only want to update records
>>>>> in classinfo table that have one or more child records in activeshow
>>>>> table.
>>>>>
>>>>> I'm guessing that there is a control on the form that has the
>>>>> "activeshow" value that you need to be using?
>>>>>
>>>>> Also, explain what you mean by ""will not work". What happens when you
>>>>> try to run this query?
>>>>> --
>>>>>
>>>>> Ken Snell
>>>>> <MS ACCESS MVP>
>>>>>
>>>>>
>>>>>
>>>>> "Dale Brown" <dalebrown@gaitkeeper.com> wrote in message
>>>>> news:uQMwS4HHFHA.3376@TK2MSFTNGP14.phx.gbl...
>>>>>> Ken,
>>>>>>
>>>>>> The select scoreid from classinfoscore will return just one record. I
>>>>>> am trying to update the scoreid field in a number of records in my
>>>>>> classinfo table. The classinfoscore has 1 record with one field
>>>>>> scoreid in it. This is a temporary table set based on what a user
>>>>>> selects in a listbox on a form. The classinfo table has classes from
>>>>>> many different shows. The joining on the activeshow table allows me
>>>>>> to tell which show the user is working on. The user will also enter a
>>>>>> starting and ending classnumber on the form. This is where the where
>>>>>> clause comes into play. Let me know if you need more information or
>>>>>> if this is enough.
>>>>>>
>>>>>> Dale
>>>>>> "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>>>>> news:upSwexHHFHA.3484@TK2MSFTNGP12.phx.gbl...
>>>>>>> You'll need to tell us what you're trying to do, including what your
>>>>>>> table structures are and such.
>>>>>>>
>>>>>>> It's not clear from the SQL statement that you've posted. Is the
>>>>>>> (select scoreid from classinfoscore)
>>>>>>> clause going to return just one record? or multiple records?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Ken Snell
>>>>>>> <MS ACCESS MVP>
>>>>>>>
>>>>>>> "Dale Brown" <dalebrown@gaitkeeper.com> wrote in message
>>>>>>> news:%23RewUuHHFHA.472@TK2MSFTNGP12.phx.gbl...
>>>>>>>>I have never done an update query in Access yet. Here is what I have
>>>>>>>>tried but it will not work. Can anyone help with the syntax?
>>>>>>>>
>>>>>>>> update classinfo
>>>>>>>> INNER JOIN activeshow ON ActiveShow.ShowID=ClassInfo.ShowID
>>>>>>>> WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
>>>>>>>> forms!ClassFill!endnbr
>>>>>>>> set scoreid = (select scoreid from classinfoscore)
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
- Next message: SteveS: "Re: Problem with query expression"
- Previous message: Ken Snell [MVP]: "Re: Update Query"
- In reply to: Ken Snell [MVP]: "Re: Update Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
