Update Query question 06-26-07

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!
0
Utf
6/26/2007 3:46:00 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1032 Views

Similar Articles

[PageSpeed] 3

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
..

"robinrd" <robinrd@discussions.microsoft.com> wrote in message 
news:F732FFF2-D0E8-4220-AC97-27A10B7C080B@microsoft.com...
> 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! 


0
John
6/26/2007 4:29:01 PM
robinrd wrote:

>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")

-- 
Marsh
MVP [MS Access]
0
Marshall
6/26/2007 5:01:10 PM
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]

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"robinrd" wrote:

> 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!
0
Utf
6/26/2007 6:01:01 PM
Reply:

Similar Artilces:

Parameter Query Question 02-15-08
I have created a series of Parameter Queries in a db (Access 2003), using the square brackets [ ] to fill - in a Criteria. How can I specify the maximum number of characters allowed in the fill-in box, for example we have state as two characters, MD, PA, Ca in the tables and wish to limit the characters in the fill-in box to match the field size. Thanks Paul PA, You can't. Your only option for that (and a much better way to go) is to create a form. Put a textbox on the form. Set the appropriat properties on the form to limit the number of characters. I would also put a butto...

TLS: Question re. Outbound TLS Behavior
I cannot find any documentation as to the behavior of this specific aspect of Microsoft's implimentation TLS... If the box for TLS is checked in the Outbound Security options on an SMTP connector, will it attempt TLS and fall back to non-TLS if the receiving server does not respond to TLS? Or, to put it another way, can both outbound TLS-encrypted messages and outbound non-TLS-encrypted messages by handled by the same SMTP connector? Does checking the box mean "Attempt" oubound TLS or does it mean "Require" outbound TLS? Here is my problem. My customer has sever...

Inbound Email 01-15-07
Hello, I have created an Auto-Reply rule from when cases get created. If a user eplies to the email, I would like it to get recorded back into the CRM, I beleieve this is possible. As there any configurations that need to happen to do this? When I reply it goes to the case owner like a normal email but does not go to the CRM as well. Thank you, Ray Not my strongest area of CRM but I believe you need a rule that forwards your mail to the CRM Mailbox. -- Rgds Michael MCDBA 2000 | MCITP DBA 2005 "rhayward@isdweb.com" wrote: > Hello, > > I have created an Auto-Rep...

Trying to do mass update on label field in MS POS 2.0
I am trying to do a mass update on all of our items to make the labels be code 128. I am doing this so that we can print new shelf tags with the bar code on them. I am trying to make sense of the database and not having much luck. Can anyone tell me the table name and column name to do this? This is the field that appears on items in the option tab under label and bar code type. There is a drop down box that allows you to select the type of bar code. My goal is to do this in a SQL statement instead of opening up each of our 1500 items and choosing this manually. Thanks Patrick Patrick,...

EXCEL IQY Queries
I have created the Excel iqy queries from the third party reporting tools and during the process there is an option either to pass the User Login id and Pass word as a parameter or not. We have developed some queries using generic id so that the users do not have to pass the Login id and password and now we want to change that. Is there a way to modify that excelquery.iqy file without redoing that. can i see the sql script of that "iqy" file. can i modify that? Please let me know. Thanks in advance!!!! ...

Dumb question... how to create a Enterprise Formula Custom Field
Guys, I'm writing because I've tried and search but can't get it ... From PWA I created a Project Custom Field "MyField" of type Text with the formula "Hello World", I tried with " and '. When I open a project the field returns #ERROR. I published to see if magic happens, but nothing. I did the same with type number and formula 10, same result. So In the end, I can't get any formula to work. What am I doing wrong ... I googled with no success. Perhaps try opening the project and hitting the F9 button to refresh calculations? Does ...

Calculating on Query
Hello, I have a table "A": A_ID A_Date A_Qty A_Factor If i wanna count "A", I use Count(A... If I wanna Addition, I use Sum(A How can I make multiplication of "A_Factor" in a query? ex: first A_Factor * second A_factor * third A_Factor ... Regards, Henderson Add the log of A_Factor and then convert the result. For example. Exp(Sum(Log(A_Factor))) If the number gets to be too large you will get an error. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. &quo...

Upgrade PS2007 from SP1 to current updates
What is the best way to update our PS2007 installation (currently on SP1) with the latest cumulative updates? Can we just install the latest update from Dec 2009 in a test environmnet or is it necessary to install other PS updates first? RQ Hi RQ, you need to install SP2 first. After that you can apply CU December. Cumulative always means since latest Service Pack. Regards Barbara "RQ" wrote: > What is the best way to update our PS2007 installation (currently on SP1) > with the latest cumulative updates? Can we just install the latest update >...

updates wont install get error message 643 & 636
how do I install them stella wrote: > how do I install them Which updates (listed by KB######)? What Operating System? What Service Pack level is your Operating System? What architecture is your Operating System (32-bit or 64-bit?) What antivirus software do you utilize? What firewall software do you utilize? What antispyware software do you utilize? What is your backup plan like (backups of your important data to external media)? What have you tried in order to resolve your issue so far? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://...

getting updated info from pivot tables
Hi everyone, I created a pivot table with months as column and sum of clients an sum of members as rows. I take the last month and Grand total number to another table I use for reporting. Is there a formula that I ca use to tell the table to get the last month (grand total - 1) and gran total numbers so when I update the pivot I dont have to change the othe table all the time. Thanks for the help, Marco -- marksuz ----------------------------------------------------------------------- marksuza's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=265 View this ...

Updater through Remote XML: Advice #2
I am implementing a very simple check routine that goes to my website and reads an XML file. The XML file basically contains all info for my apps and their current status. All I need to do is just read the version node and compare it to the current node and then advise the user to visit the website for the new version. Ive looked at ClickOnce and other methods but I would like to implement something simpler. Anyway, has anyone done this before? My XML will look something like this: <?xml version="1.0" encoding="utf-8"?> <Apps> <App id="0001"...

Looping Through Rows Question
I'm using Visual Basic 6.5 I need to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I have the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000? Any assistance would be greatly appreciated - Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long ...

MM 2004
I have noticed the past few times I have tried to get updates, I get the following error: Money encountered an error processing the downloaded file ('SMRTINET.INI'). Any ideas??? Chris Chris, I was encountering the same error and followed these steps to correct it. 1. Open Control Panel. 2. Open Internet Options. 3. Click on "Delete Files..." (Temporary Internet Files). 4. Click on "Delete Cookies..." (although I'm pretty sure this step isn't required) 5. Open and close Microsoft Money again...the error should be gone. You can also access the I...

Query Count if Function QQQQssss
I have a range where Attendees of a convention either pre-register, Compted, Pre-register Only, or Register on site. For the on-site regitered they can register on Friday Saturday or Sunday. (that is a Different Range) I want to count "Registered" In the registered Range; From each Day EG Friday, Saturday, Sunday (Date Range) I can countif Dates and Registered but how do I make it count on two different Cryteria Hi Mark! You can use SUMPRODUCT as one approach to counting on more than one criteria: =SUMPRODUCT((WEEKDAY($A$1:$A$24)=1)*($B$1:$B$24="Registered")) -- -- R...

Excel 2007, Basic, Square-1 Opening Question
Clicking on an xls or an xlsx file opens Excel 2007; however, the file itself does not load/open (no blank sheet either). Only way is to drag the file from its directory. What have I done wrong? Tks! Wayne Try minimizing and then maximizing Excel to see if that makes the file appear. If it does, I'll build a small routine that does that automatically when a file is opened. This appears to be a very frequent problem for some Excel 2007 users. Bob Flanagan <wgd.roaming@verizon.net> wrote in message news:9jg3b49oe5t308vjcngefg41f5htajrm2r@4ax.com... > Clicking on an ...

Append Query to Mainform and Subform
I am using Access 2007. The problem is that my append query for the subform is not updating the online SQL Server table called dbo_Actions_local. The apend query for the main form though is updating the online table called dbo_Contacts. I am not getting any errror messages when I fire the event. First the query for the mainform is fired with a OpenQuery action and then the one for the subform. I have two append quries that are connected to a form, the main form also includes a subform. I will explain the structure of my tables. Local tables: Contacts (PK=ID) Actions_local(PK=...

auto-updating the auto-updater so 12.0.1 can be installed forces a complete delete and reinstall of office 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Okay - I must confess I never thought that the mac geeks that work for microsoft had any affinity for writing bad code, but i definately found some in the auo-updating system - which after a reinstall didnt recognise that 12.0.1 OR its own update were ready for download - i had to download 12.0.1 as a .dmg and have NO idea how to force the upgrader program to upgrade itself like its supposed to -- because it DID SO ONCE, and as of this moment none of the applications that are updated by that package refect that they have...

EXCEL and Query
Microsoft Query won't give me access to data in a Microsoft Excel list. After connecting to the data source (another EXCEL spreadsheet) the error message 'No tables visible' is displayed. I have named the source spreadsheet as described in the on- line help, but still no result. Is there something else I need to switch-on? Help please. Have you tried giving a name to the table in the source spreadsheet. Eg. Data is in A1:F2500 - highlight that area and go Insert / Name / Define.... call it MyData - that table name should become visible when you do the query from the ot...

automatically assign value to query
Hello, Ive got a database where staff login just using their name, and the login button has a macro assigned as follows Condition : Not IsNull([cboCurrentEmployee]) Action : SetTempVar Arguments : CurrentUserID, [cboCurrentEmployee] Certain contacts in my database are assigned to a member of staff and call sheets are set up using a query that requests that member of staffs name. Is there a way of automatically setting the criteria of the query to only show clients that are assigned to the member of staff that has logged in? Thanks, please let me know if i need to explain th...

Complex Query question
I've got a challenging query I need help with! Here's a snapshot of the tables and relationships. I've put the relevant tables into this query layout just for illustrative purposes. ----- click on it to make it bigger (it's still a little hard to read) http://tinypic.com/view.php?pic=6xu9oh4&s=1 Note - the table names start with phrases that are not relevant to this question (ie I use the word "record" for another use than the normal database usage of that word). ------ I'll explain the setup. I want the Table Record-Orders-Sales to lookup Record-Cur...

e-mail address privacy question
I have Outlook 2007 and I want to e-mail all my past customers about a new product introduction. I haven't sent out a mass mailing like this before. If I put all their names in the "To" field, each recipient sees everyone else's e-mail address. I don't imagine that the recipients won't want their e-mail addresses to be public like this. Please what's the best way to send my e-mail out so that all e-mail addresses are hidden from each other so receipients don't get to see everyone else's e-mail address? Would I just put the list of e-mail addresses...

IE will not access Windows update page
Internet Explorer cannot display the webpage browser address shows: <http://windowsupdate.microsoft.com> "updates are ready for your computer "KB952069" will not install and notification continues to pop up after automatic updates says "Installation Compete" Dell Mini 1010 Windows XP Home 32bit SP3 1gb RAM I cannot access Microsoft Windows Updates. All other sites work just fine. Original issue: Could not install any programs Software ran: Malwarebytes, Adaware, Spybot, Trojan Remover and CCleaner After finding various critters affecting PC...

Error on Windows Update
Im not sure why but my labtop asus does not seem to allow me to get updats or recieve any it keeps poping up as error C004F012..if anyone can help me plz contact me at cory_mommyboy11@yahoo.com Please post your internet drivers license number if you need assistance. cory_mommyboy11@yahoo.com wrote: > Im not sure why but my labtop asus does not seem to allow me to get updats > or recieve any it keeps poping up as error C004F012..if anyone can help me > plz contact me at cory_mommyboy11@yahoo.com ...

Portfolio not updating stock prices properly
Some of my stocks in my portfolio do not update prices throughout the day. Some do, but most do not, including HD, T, ... ones that I know have the correct symbol inputted. For instance, at 7PM, when I look at my portfolio, most of the stocks list the days gain as unch, and still list yesterday's closing price (which didn't update until sometime late the night before). For those stocks, I never have a day's gain listed, because when the price finally updates, it's a new day!! Strange but true...any suggestions? I've had the same problem since last October except t...

Microsoft Office 2004 for Mac 11.3.7 Update posted
August 14, 2007 Microsoft's Macintosh Business Unit (MacBU) today released Microsoft Office 2004 for Mac 11.3.7 Update. "This update fixes a vulnerability that an attacker can use to overwrite the contents of your computer's memory with malicious code. For more information about this update, see the Microsoft Knowledge Base article (KB940763)." Before you install this update, make sure that the Microsoft Office 2004 for Mac 11.3.6 Update is installed on your computer. To get the latest announcements, subscribe to the Entourage Help Blog. <http://blog.entourage.mvps.org/...