Update Query question

[robinrd]

Hello, I am trying to write what I assume would be an update query. I have 2
fields, acct_num and brnch_num in my table. I need to combine these 2 fields
into a new field called cust_num. So, if:

brnch_num acct_num
123 45678
123 12
12 1

Then I would need the cust_num to read: 12345678, 12300012, 01200001

I'm not sure how to get the zeroes into the cust number so that that number
is always 8 diguts. Any help would be greatly appreciated!

 

[John Spencer]

I wouldn't use an update query for this. Keep the data separately and use a
calculated column whenever you need the customer number

The following may give you what you want to see:

Field: Cust_Num: Right("000" & brnch_Num,3) & Right("00000" + Acct_Num,5)

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

 

[Marshall Barton]

Hello, I am trying to write what I assume would be an update query. I have 2
fields, acct_num and brnch_num in my table. I need to combine these 2 fields
into a new field called cust_num. So, if:

brnch_num acct_num
123 45678
123 12
12 1

Then I would need the cust_num to read: 12345678, 12300012, 01200001

I'm not sure how to get the zeroes into the cust number so that that number
is always 8 diguts. Any help would be greatly appreciated!

You should NOT put that kind of data in a table.

Since the individual values are all in the record, you can
calculate the cust number any time you want to display it on
a form or report. Just use a text box with an expression
like:
=Format(brnch_num, "000") & Format(acct_num, "00000")

 

[Jerry Whittle]

First off it would not be an update query. Please keep the brnch_num and
acct_num in seperate fields and do not create a third cust_num field. Some
day you'll be glad that you did. For example when you need to find out
information about a particular branch.

What you want to do is combine the two fields together in queries, forms,
and reports as needed. Below will work in a query. Having to pad out the
leading 0 in the brnch_num caused the most problems. I assumed that the
brnch_num will always be a maximum of 3 character and acct_num will always be
no more than 5 characters.

cust_num: String(3-Len([brnch_num]),"0") & [brnch_num] &
String(5-Len([acct_num]),"0") & [acct_num]