459,663 Members | 1,431 Online
Need help? Post your question and get tips & solutions from a community of 459,663 IT Pros & Developers. It's quick & easy.

# Combining numbers

 P: n/a Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 700 001 700001 700 002 700002 .. .. .. 700 444 700444 c D RESULT 701 000 701000 701 001 701001 701 002 701002 .. .. .. 701 444 701444 I have two problems that I need some some help with. 1. I'm having a problem in combining the numbers..when I try to combine them using a expression ([A]+[b]) in a query, I get the results of adding the two fields..not the combining them. 2. Once I can get the two fields combined, I'm going to have to sort the whole thing as follows 700000 701000 700001 701001 700002 701002 .. .. .. 700444 701444 I'm a beginner at using access...I realize it's potential, but little things like this really trip me up. Feel free to emai me with suggestions Thanks, Mark Nov 13 '05 #1
5 Replies

 P: n/a "M.Stanley" wrote in message news:79*************************@posting.google.co m... Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 [snip] I have two problems that I need some some help with. 1. I'm having a problem in combining the numbers..when I try to combine them using a expression ([A]+[b]) in a query, I get the results of adding the two fields..not the combining them. In Access the operator for concatenating text is the & symbol. The + symbol can be used if the data is clearly text (contains alpha characters), but as you have found if the text contains only digits Access will assume that the + should be applied as an arithmetic addition rather than concatenation. 2. Once I can get the two fields combined, I'm going to have to sort the whole thing as follows 700000 701000 Once you have the desired output in the query simply add "Ascending" in the sort row of the query design grid underneath the desired column. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Nov 13 '05 #2

 P: n/a ms********@hotmail.com (M.Stanley) wrote in news:79*************************@posting.google.co m: Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 700 001 700001 700 002 700002 . . . 700 444 700444 c D RESULT 701 000 701000 701 001 701001 701 002 701002 . . . 701 444 701444 I have two problems that I need some some help with. 1. I'm having a problem in combining the numbers..when I try to combine them using a expression ([A]+[b]) in a query, I get the results of adding the two fields..not the combining them. what you want to do is create a string that contains the two numbers. Use the concatenation operator, (&) instead of the summation operator (+). The problem is that leading zeroes sometimes get messed up, so 700 000 mifht become 7000 and 701 001 shows as 7011. There's a fix for that too. It's the format function, so the expression in your query winds up being Format([A],"000") & format([b],"000") 2. Once I can get the two fields combined, I'm going to have to sort the whole thing as follows 700000 701000 700001 701001 700002 701002 . . . 700444 701444 in the query grid, put a duplicate set of the fields you want to sort on, in the order to sort by starting from left to right, in your case b then a. uncheck the show: checkbox and choose ascending in the sort: field I'm a beginner at using access...I realize it's potential, but little things like this really trip me up. Feel free to emai me with suggestions Bad thing to ask. Responses should go to the newsgroup so that everyone can participate, and learn. Thanks, Mark -- Bob Quintal PA is y I've altered my email address. Nov 13 '05 #3

 P: n/a Thanks for your help; I'm going to try your fixes ASAP and post my results to the group. Mark Bob Quintal wrote in message news:... ms********@hotmail.com (M.Stanley) wrote in news:79*************************@posting.google.co m: Hi, I'm attempting to create a query that will combine 2 columns of numbers into one. The followng comes from 1 table with 4 fields (A,B,C,D) A B RESULT 700 000 700000 700 001 700001 700 002 700002 . . . 700 444 700444 c D RESULT 701 000 701000 701 001 701001 701 002 701002 . . . 701 444 701444 I have two problems that I need some some help with. 1. I'm having a problem in combining the numbers..when I try to combine them using a expression ([A]+[b]) in a query, I get the results of adding the two fields..not the combining them. what you want to do is create a string that contains the two numbers. Use the concatenation operator, (&) instead of the summation operator (+). The problem is that leading zeroes sometimes get messed up, so 700 000 mifht become 7000 and 701 001 shows as 7011. There's a fix for that too. It's the format function, so the expression in your query winds up being Format([A],"000") & format([b],"000") 2. Once I can get the two fields combined, I'm going to have to sort the whole thing as follows 700000 701000 700001 701001 700002 701002 . . . 700444 701444 in the query grid, put a duplicate set of the fields you want to sort on, in the order to sort by starting from left to right, in your case b then a. uncheck the show: checkbox and choose ascending in the sort: field I'm a beginner at using access...I realize it's potential, but little things like this really trip me up. Feel free to emai me with suggestions Bad thing to ask. Responses should go to the newsgroup so that everyone can participate, and learn. Thanks, Mark Nov 13 '05 #4