how to renumber field in table

[Jon]

Greeting,

I have two tables, one for Branch and one for Expenses
What I want to do is creating command button that once it pressed, I want it
to take the Branch number and look for the last number in expenses table and
add it with branch number. For example: the branch number is 10 and the last
record is 66 I want the branch number to be 1066 in the field of branch
number. How to do that please??

 

[Arvin Meyer [MVP]]

An update query:

UPDATE MyTable SET [Branch Number] = [Branch Number] & Max(RecordID);

 

[John Spencer]

Arvin and Jon,

I think that will fail as an update query. You'll probably get a "must
use an updateable query" error.

UPDATE MyTable
SET [Branch Number] = [Branch Number] &
DMax("RecordID","MyTable","[Branch Number] =" & Chr(34) & [Branch
Number] & Chr(34))

If Branch number is a number field instead of text, remove the Chr(34)
from the query. And if all you want is the maximum number for RecordID
drop the entire third argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Greeting,

I have two tables, one for Branch and one for Expenses
What I want to do is creating command button that once it pressed, I want it
to take the Branch number and look for the last number in expenses table and
add it with branch number. For example: the branch number is 10 and the last
record is 66 I want the branch number to be 1066 in the field of branch
number. How to do that please??

You've gotten some answers how... but I must ask WHY? Are you *STORING* this
calculated value? Be aware that it will be *wrong* the moment a new record is
added for the branch.

Storing two pieces of information in one field is *never* a good idea, and
storing derived data is even worse. What's the purpose of this field?

 

[Arvin Meyer [MVP]]

oops! I you're right. I used an aggregat instead of a domain aggregate.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin and Jon,

I think that will fail as an update query. You'll probably get a "must
use an updateable query" error.

UPDATE MyTable
SET [Branch Number] = [Branch Number] & DMax("RecordID","MyTable","[Branch
Number] =" & Chr(34) & [Branch Number] & Chr(34))

If Branch number is a number field instead of text, remove the Chr(34)
from the query. And if all you want is the maximum number for RecordID
drop the entire third argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

An update query:

UPDATE MyTable SET [Branch Number] = [Branch Number] & Max(RecordID);