concatenating two vendor codes

My company recently changed the vendor codes and now I end up with two
separate sets of data for each vendor. Obviously I could just leave
the vendor code field out. I don't want to do that so what I need to
do is concatenate the two codes. Example

Vendor Code    Vendor Name
123                    Joe's Supplies
ABC                  Joe's Supplies

what I need is this

Vendor Code     Vendor Name
123/ABC             Joe's Supplies

any ideas?
0
pat67
1/20/2010 7:41:28 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
822 Views

Similar Articles

[PageSpeed] 55

Rather than create a new record for each vendor, add another field to the 
table -- call it NewVendorCode -- and put the new value in that. Then, in a 
query you can concatenate them as:

FullVendorCode: [VendorCode] & "/" & [NewVendorCode]

-- 
Lynn Trapp
MCP, MOS, MCAS


"pat67" wrote:

> My company recently changed the vendor codes and now I end up with two
> separate sets of data for each vendor. Obviously I could just leave
> the vendor code field out. I don't want to do that so what I need to
> do is concatenate the two codes. Example
> 
> Vendor Code    Vendor Name
> 123                    Joe's Supplies
> ABC                  Joe's Supplies
> 
> what I need is this
> 
> Vendor Code     Vendor Name
> 123/ABC             Joe's Supplies
> 
> any ideas?
> .
> 
0
Utf
1/20/2010 8:30:01 PM
On Jan 20, 3:30=A0pm, Lynn Trapp <LynnNoSpam.TrappNoS...@hotmail.com>
wrote:
> Rather than create a new record for each vendor, add another field to the
> table -- call it NewVendorCode -- and put the new value in that. Then, in=
 a
> query you can concatenate them as:
>
> FullVendorCode: [VendorCode] & "/" & [NewVendorCode]
>
> --
> Lynn Trapp
> MCP, MOS, MCAS
>
>
>
> "pat67" wrote:
> > My company recently changed the vendor codes and now I end up with two
> > separate sets of data for each vendor. Obviously I could just leave
> > the vendor code field out. I don't want to do that so what I need to
> > do is concatenate the two codes. Example
>
> > Vendor Code =A0 =A0Vendor Name
> > 123 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Joe's Supplies
> > ABC =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Joe's Supplies
>
> > what I need is this
>
> > Vendor Code =A0 =A0 Vendor Name
> > 123/ABC =A0 =A0 =A0 =A0 =A0 =A0 Joe's Supplies
>
> > any ideas?
> > .- Hide quoted text -
>
> - Show quoted text -

I can't really do it that way since the table is updated from another
file that only has one code. What I did was create a table based off
of the original table and then concatenated. Then joined that query to
any other query i needed. I was just seeing if there was a way to do
it without an extra table
0
pat67
1/20/2010 8:58:05 PM
Reply:

Similar Artilces:

Remove text "Discount code: **" from receipt??
Hello, I have a weird problem within a receipt. I got two registers, with different receipt.xml linked. Register 1 prints on the receipt the text "Discountcode: ***" when an item is sold with discount. Register 2 doesn't print this text, even if I use the same receipt format?? I can't get what the problem is? Hope somebody can help me. Greetings Raymond Bakker the Netherlands Are you using windows drivers on one of the printers? "Raymond Bakker" <raymond.bakker@rebus-it.nl> wrote in message news:1C61DF7A-1E93-4BD2-ABDF-46731BDC9B25@microsoft.com.....

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

how to compare two bitmaps and give a mark to describe how similar they are
hi, everyone how to compare two bitmaps and give a mark to describe how similar they are? The two bitmaps's size are the same, I can use CDC::GetPixel to get a mark, but this method is inefficient. Any body have any ideas? Thank you very much. take the pixel value of one minus the pixel value of the other and square this difference. So it goes something like difference = 0; for (x=0....) for (y=0....) difference += pow( bitmap1(x,y) - bitmap(x,y) ), 2) You could also use a cross correlation, but that has a built in bias such that a difference around white is more import...

Compare Two Workbooks
I'm trying to write a piece of VBA that will check each cell in a workbook against each corresponding cell in another workbook, and highlight where there are differences. I'm really falling over at the first hurdle, as I cant get my head around how to reference the cells. I've got variables that tell me the workbook, the worksheet, the row and the column, but I don't appear able to so something as simple as check if wb1.ws1.cell1 = wb2.ws2.cell2. The code I have so far is below: Sub test() Dim wb1 As Workbook Dim wb1name As String Dim wb2 As Workboo...

zip codes starting with 0
When doing a mail merge from excel to word zip codes starting with 0 (zero) drop the first number "0" from the address. ie zip code 01560 prints out as 1560. How do I get all 5 numbers to show? An Excel way: Format all your Zip Codes as Text. A Word way: Before you start the merge, Tools | Options | General. Place a check next to Confirm merge at open. Start the Merge. When you go to pick the Data Source, a box will pop up. Pick wirksheets via DDE. tj "Joe Mac" wrote: > When doing a mail merge from excel to word zip codes starting with 0 (zero) > drop the fi...

VBS script, Sync two folders accross networks
Hi, I need a VBS script to sync to folder accross a network. So the source would be \\servername\sharename\syncfolder and the destination would be \\server2\share2\sync2. Can someone please help. Submitted via EggHeadCafe - Software Developer Portal of Choice A Good Solution for "Magic String" Data http://www.eggheadcafe.com/tutorials/aspnet/b916e3a9-d056-4669-8bf3-aa98ed6669c3/a-good-solution-for-magi.aspx "mattthew Pitera" schrieb im Newsbeitrag news:2009129181729matt.pitera@gmail.com... > Hi, > I need a VBS script to sync to folder accross a net...

Nesting or joining two formulas???
I currently have two formulas that I'm needing to somehow join togethe but I have not been able to do this legally. {This formula gives a sum of any time over 8 hours and displays i using a decimal value.} My cells are formatted to 0### for cells C10 through F10 and cell H1 is converted to decimal using the formula below. [this formula located in H10] =IF((F10-E10)+(D10-C10)>800, (F10-E10)+(D10-C10)-800, 0)/100 {I still needed the above formula to display in quarter hou increments, ie: .15 = .25, .30 = .50 etc. This was submitted to me b JanetW and works great seperately when pla...

Two columns into one row
I have some files I need to format. I have a list of coordinates that are in two columns and I need to turn them into one row, keeping the order ie: 1 2 3 4 5 6 7 8 9 10 to 1 2 3 4 5 6 7 8 9 10 Is there anyway I can convert these two columns into rows without cutting and pasting the data individually? -- alih ------------------------------------------------------------------------ alih's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36023 View this thread: http://www.excelforum.com/showthread.php?threadid=558128 This will do what you want =A1&&qu...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Zip Code Last 4 Digit as Zero
I have a Zip Code Table with only the first 5 digits and do not have the last 4 digit. How can I make them in 9 digit zip code with the last 4 digit as 0000?. manually I've been doing this (01234 then add the last digit as -0000) which come to this 01234-0000. With a five digit ZIP code in A1, in another cell: =A1 & "-0000" -- Gary''s Student - gsnu201003 "cheppy" wrote: > I have a Zip Code Table with only the first 5 digits and do not have the last > 4 digit. > How can I make them in 9 digit zip code with the last 4 digit...

two more questions
Can I lock some columns on a sheet so they are not editable ? And I am new to excel and needed to calc several fields from different worksheets onto one "totals" worksheet. How do I do this? Thanks over and over again : ) Mary, by default all cells in excel are locked, if you only need a few locked I would select them all first, Ctrl A, then go to format, cells, protection and uncheck locked, then select the cells or columns you want to lock and go to format cells and check locked, the go to tools, protection, and protect sheet, enter a password if you want, now the cells that ar...

Compare two sheets...
Any one know how can I compare two Excel Sheets? Thanks Compare them for what? If they are formatted the same and you are comparing numbers, copy one sheet, then select the numbers from the other sheet and go to this copy. Select the same area and do Edit=>PasteSpecial and select values and subtract. That will show you the numeric differences. -- Regards, Tom Ogilvy "Mike Hunt" <nojunk@please.com> wrote in message news:%23I0V0AAiDHA.2296@TK2MSFTNGP09.phx.gbl... > Any one know how can I compare two Excel Sheets? > > Thanks > > Go to this website an...

Adding code right after the "Save As" dialog box
I have created an excel application whose footer has -apart from other things- the full path and file name where the excel workbook resides. I would like this information (fullpath + file name) to be reflected in the footer when my user clicks on “Save as”. How can I invoke an event and make decisions –change the footer- based upon what my user has entered in Save As? The ThisWorkbook module has a BeforeSave event for ThisWorkbook where you should be able to do that in. -- Rick (MVP - Excel) "Jess" <Jess@discussions.microsoft.com> wrote in message ne...

IF and Concatenate
Hi I have a following chart which list out delivery dates arcross the top with items and units on the body of the chart (dashes are spaces) On the right column is the results that I need. What kind of formula can I use to return such results? I thought that I can use IF and Concatenate formula (IF, ordered units, then seek out date....). Not even sure I can use IF, since in my real chart, I have more than 20+ dates going across. Any help would be much appreciated! Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result 66106-------------------------------9--------5--------9 DUE ...

VBA code to paste data based on condition
I'm new to VBA programming and would appreciate some help with a macro. There is a database table that can be refreshed throughout the month that simply overrides the cell value from the same refresh. So the values update every time you refresh in the same cell until the next month begins (Date + Year are the column headings). What I need to do is break each month down into weeks like this (1-7 = Week 1, and so on where Week 5 is any day after the 28th of the month). I need a macro where the morning a new week starts (Day 8), I can copy/paste values the entire column in...

One worksheet, two computers
Hello, I have an excel worksheet that needs to be accessed by two computers a the same time and be updated without having to copy from a mapped drive or share folder, and I have no server. What should I do? Thank you in advance, Davi -- david74 ----------------------------------------------------------------------- david747's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3521 View this thread: http://www.excelforum.com/showthread.php?threadid=54978 Google Spreadsheet? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "dav...

averaging a value between two serial dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

two MS POS
Is it possible to connect two MS POS to share database in one PC. I have a customer who purchased two MS POS and installed on two separate PCs. Now they want to share database and transaction. Please advise me how. thanks. ...

Replacing a sentence with VBA-code
One my form includes a control to calculate amount of holes. I am using following sentence to calculate: Holes: IIf([lenhgt] Between 120 And 800;2;IIf([lenhgt] Between 800 And 1350;3;IIf([lenhgt] Between 1350 And 1900;4;IIf([lenhgt] Between 1900 And 2450;5;IIf([lenhgt] Between 2450 And 3000;6;IIf([lenhgt] Between 3000 And 3500;7)))))) Measures are as millimeter. There is another control on the form to calculate the distance of holes. Is there sense to change sentences to VBA-code? Both of sentences return right values. If so, what kind of code is needed? -- Have a Nice Day! Hi Mark...

When concatenating concatenates don't concatenate... #3
Sometimes the simplest thing... I didn't realise that all the files ha to be open for the concatenation to work :rolleyes: . Doing s eliminates the problem and explains the erratic behaviour. And yes, th idea is to blend the variable and unvariable parts of an html pag together by pasting the final concatenation into a notepad.txt file Primitive, but interim. Thank you very much for your help. Best regards d'A -- d'A ----------------------------------------------------------------------- d'Az's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 ...

Concatenate cells without specifying/writing cell address individually
Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&...

Concatenate with delimiter
I have some text that I need to concatenate, but with a delimiter. Three strings, A, B, C, where A and C are complicated expressions and B is a space-dash-space. I want the separator only if BOTH A and C are non-empty, otherwise only A or C (or nothing, if they're both empty). I've been playing with IsNull and NullIf functions, trying to influence the concatenation, but haven't come up with anything useful. Doesn't T-SQL have a function that is the opposite of Split? Pete -- This e-mail address is fake, to keep spammers and their address harvesters ou...

Coding letters and numbers
Is there a way to have a monetary vaue appear when a letter is typed into a cell? Tools>Autocorrect options If this is not at all what you mean, try being a bit more specific -- Kind regards, Niek Otten "Linds" <Linds@discussions.microsoft.com> wrote in message news:B6778AA0-9164-4A1A-981A-7C163B67009C@microsoft.com... > Is there a way to have a monetary vaue appear when a letter is typed into > a > cell? Maybe you could use a table on another worksheet and a helper cell that would contain an =vlookup() formula. =if(a1="","",vlookup...