copy value from one field to another in Update

[Associates]

Hi,

I was wondering if i could get some help with the query i am having trouble
with. I was trying to update some data in a table called "Students". So below
is my update query.

Set WorkBase = CurrentDb

Updatequery = "UPDATE [Invoices]"
Updatequery = Updatequery & " SET myCurrentInvoice2 = 0,
RemainingInvoice2 = 0"
Updatequery = Updatequery & " WHERE Project_No like '" &
Me.LB_JobList.Column(0) & "'"

WorkBase.Execute Updatequery, dbFailOnError
WorkBase.Close

I know that with the above query, it will update every records whose Project
No is the same as that of Column(0).

However, there is one thing i want it to do. I want it to update one field
(say, PreviousInvoice) as the query is being executed and the value of
PreviousInvoice is the same as PreviousInvoice2 whose value has already been
determined. So I was trying to copy from the value of PreviousInvoice2 to
PreviousInvoice. I modified the code as below

Updatequery = "UPDATE [Invoices]"
Updatequery = Updatequery & " SET myCurrentInvoice2 = 0,
RemainingInvoice2 = 0, PreviousInvoice =" & PreviousInvoice2
Updatequery = Updatequery & " WHERE Project_No like '" &
Me.LB_JobList.Column(0) & "'"

I got error on this as i expected. How do i get around this?
PreviousInvoice2 and PreviousInvoice are both part of "Invoices" table
fields. Therefore, PreviousInvoice2 is not a variable that user enters in.

Any helps would be greatly appreciated

 

[GBA]

write your query using the query design tool, rather than with vb. Then once
you have it working manually - change the design mode into sql view; then
you will have the working code that you can use in your vb

 

[Michel Walsh]

And to specify data from another row of the same table, bring the table
TWICE in the designer. One of the reference will get an _1 appended to its
name. Add the required criteria, under columns of this reference, to
identify the unique row you want to reach, and in the end, you use something
like:

SET table.field = table_1.otherField



Sure, the query must be updateable, too. So, if the criteria involving
table_1 depends on the 'record to be updated', ie, from table (without
_1 ), then a join rather than a WHERE clause may be preferable. You can also
use sub-query (but loosing part of the graphical interface) :


SET table.field = (SELECT table_1.otherField FROM table AS table_1
WHERE ... )



It *may* be slower than a join, though, but it may allow to do things that a
join cannot do, while keeping the updateability.



Vanderghast, Access MVP

write your query using the query design tool, rather than with vb. Then
once
you have it working manually - change the design mode into sql view; then
you will have the working code that you can use in your vb

Hi,

I was wondering if i could get some help with the query i am having
trouble
with. I was trying to update some data in a table called "Students". So
below
is my update query.

Set WorkBase = CurrentDb

Updatequery = "UPDATE [Invoices]"
Updatequery = Updatequery & " SET myCurrentInvoice2 = 0,
RemainingInvoice2 = 0"
Updatequery = Updatequery & " WHERE Project_No like '" &
Me.LB_JobList.Column(0) & "'"

WorkBase.Execute Updatequery, dbFailOnError
WorkBase.Close

I know that with the above query, it will update every records whose
Project
No is the same as that of Column(0).

However, there is one thing i want it to do. I want it to update one
field
(say, PreviousInvoice) as the query is being executed and the value of
PreviousInvoice is the same as PreviousInvoice2 whose value has already
been
determined. So I was trying to copy from the value of PreviousInvoice2 to
PreviousInvoice. I modified the code as below

Updatequery = "UPDATE [Invoices]"
Updatequery = Updatequery & " SET myCurrentInvoice2 = 0,
RemainingInvoice2 = 0, PreviousInvoice =" & PreviousInvoice2
Updatequery = Updatequery & " WHERE Project_No like '" &
Me.LB_JobList.Column(0) & "'"

I got error on this as i expected. How do i get around this?
PreviousInvoice2 and PreviousInvoice are both part of "Invoices" table
fields. Therefore, PreviousInvoice2 is not a variable that user enters
in.

Any helps would be greatly appreciated