how to make query results in table 10 characters long?

**jgomez** · 08-12-2011, 11:21 AM

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?

**jeffreybrown** · 08-12-2011, 11:26 AM

How about apply a cusom format to the columns...

0000000000

**jgomez** · 08-12-2011, 11:38 AM

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

**jeffreybrown** · 08-12-2011, 11:52 AM

Are you looking for the answer >> 8560

Try...

[OBG_NO]+[OBL_NO]

**jgomez** · 08-12-2011, 11:56 AM

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

**jgomez** · 08-12-2011, 11:58 AM

no not looking to sum them looking to combine field 1 & 2. I want field 3 to be 00000000120000008548.

**jeffreybrown** · 08-12-2011, 12:00 PM

In Excel you could add in column C

=TEXT(A1,"0000000000")&TEXT(B1,"0000000000")

**jgomez** · 08-12-2011, 12:02 PM

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

**jeffreybrown** · 08-12-2011, 12:11 PM

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?

**jgomez** · 08-12-2011, 12:18 PM

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

**jgomez** · 08-19-2011, 03:00 PM

Format([OBG_NO],"0000000000")

this is the answer.

how to make query results in table 10 characters long?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Re: how to make query results in table 10 characters long?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions