Hi there got a problem don't know if its even possible to solve.
I'm running a query that exports into a table in access. Two of the fields give me results made of numbers ex.
field 1 field 2
123 25
156455 1158
I would like to somehow make those results into 10 digits long
field 1 field 2
0000000123 0000000025
0000156455 0000001158
any ideas?
Last edited by jgomez; 08-19-2011 at 03:00 PM.
How about apply a cusom format to the columns...
0000000000
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
it works! I didn't know that was possible. kind of new at this as you can see. Only thing is that I have field 3 with:
[OBG_NO] & "" & [OBL_NO] that combines field 1 &2
Because once this query goes into a table i'm running another query based off those numbers...
here are my new results
field 1 field 2 field 3
0000000012 0000008548 128548 <-- I formated that field as well but that doesn't change anything. Any ideas???
Are you looking for the answer >> 8560
Try...
[OBG_NO]+[OBL_NO]
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
well when i export into excel the format works for field 1 & 2... doesn't work for field 3. When i run so it can me a table it doesn't have any of my changes. I have to manually format those two fields... which is no big deal but i'm going to make a button that run all three queries automatically and it work if field 3 is not formatted right...
no not looking to sum them looking to combine field 1 & 2. I want field 3 to be 00000000120000008548.
In Excel you could add in column C
=TEXT(A1,"0000000000")&TEXT(B1,"0000000000")
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Yes but i'm not planning to export to excel... planning on running to create it as a table so i can use on another query. Trying to automate the whole process...
Ok sorry about that but this is confusing because you mentioned both Access and Excel.
In the end, you want this completely done in Access?
When we apply the custom format to 12 we get, 0000000012 and for 8548, we get 0000008548 and when you combine them together you get 128548, but you want it to show as 00000000120000008548.
Is this the value you are going to lookup from somewhere else? Because right now it is just a mask so the real values are 12 and 8548
Can you walk me through this a little more?
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
My bad.
Yes all in access.
Yes, that's what I would like for it to show.
Yes those values are pulled from database "A" which I will then use to pull more values from databse "B".
Basically database A gives me account #'s cut in half like 123 18. I need them to be like 00000001230000000018 (20 characters long) because that's the only field I can use to link up to database "B".
Format([OBG_NO],"0000000000")
this is the answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks