I can't get numbers in a special format

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

Hi I have a table in which I have 5 numeric fields containing two-digit
numbers. The format of those fields is "00" so for example number 5 is
entered 05. Now I have a querie in which I want to have those 5 fields in
one field ( [field-1] & [field--2] & [field--3] & [field--4] & [field--5])
but if the numbers of those fields are 01, 05, 09, 11, 15 the new field in
the querie shows me 1591115 and not 0105091115 which I need. I want the
zeros to be shown before each number if it is only one digit. I hope I
explained it well because my english is not very good. Thank you in advance.
If someone can help me please with step by step instructions because I am
new in Access.
 
The format is just a "visible" item, that data isn't stored that way. In
your query, include the format command to get what you want.

Instead of:
[field-1] & [field--2] & [field--3] & [field--4] & [field--5]

try:
Format([field-1], "00") & Format([field--2], "00") & Format([field--3],
"00") & Format([field--4], "00") & Format([field--5], "00")
 
Jimmy,

You can get the result you want in your query by putting the following in
the first row of an empty column in your query's design grid:

MyCalcField: Format([field-1], "00") & Format([field--2], "00") &
Format([field--3], "00") & Format([field--4], "00")


hth,
 
Thank you both so much. You helped me a lot. I would like to ask you
something else which I have been told is impossible but maybe someone does
have an idea. Can I get the numbers of those five fields in the new field of
the querie which we have formatted as needed, sorted from lower to higher.
For example the 5 numbers of the 5 fields are:
Field-1= 06
Field-2= 03
Field-3= 15
Field-4= 10
Field-5= 07
with the new field in the querie I get 0603151007
is it possible to get 0306071015
meaning have those fields sorted (A to Z) 03,06,07,10,15
Sorry if I'm asking too much but it seems noone can give me an answer to
that.
Thanks again
Jimmy
 
Go to your table in design view and change the data type of the field holding
the digits to text rather than number. You will then have to update the existing
data so that where there is a 5 for example it is changed to 05. Also your data
entry needs to be set up so that 05 has to be entered and 5 is unacceptable.

The alternative where you can use the existing data is to change your formula
to:

Format( [field-1],"00") & Format([field--2],"00") & ........
 
I woudn't know how to do this with just a query. You could load the numbers
into an array and run a sort routine on the array then concatenate them
together. This would be using VBA code. You could make the code a function
and call the function from a query, passing the field values as parameters.

--
Wayne Morgan
MS Access MVP


Jimmy said:
Thank you both so much. You helped me a lot. I would like to ask you
something else which I have been told is impossible but maybe someone does
have an idea. Can I get the numbers of those five fields in the new field of
the querie which we have formatted as needed, sorted from lower to higher.
For example the 5 numbers of the 5 fields are:
Field-1= 06
Field-2= 03
Field-3= 15
Field-4= 10
Field-5= 07
with the new field in the querie I get 0603151007
is it possible to get 0306071015
meaning have those fields sorted (A to Z) 03,06,07,10,15
Sorry if I'm asking too much but it seems noone can give me an answer to
that.
Thanks again
Jimmy




Jimmy said:
Hi I have a table in which I have 5 numeric fields containing two-digit
numbers. The format of those fields is "00" so for example number 5 is
entered 05. Now I have a querie in which I want to have those 5 fields in
one field ( [field-1] & [field--2] & [field--3] & [field--4] & [field--5])
but if the numbers of those fields are 01, 05, 09, 11, 15 the new field in
the querie shows me 1591115 and not 0105091115 which I need. I want the
zeros to be shown before each number if it is only one digit. I hope I
explained it well because my english is not very good. Thank you in advance.
If someone can help me please with step by step instructions because I am
new in Access.
Click to expand...
Click to expand...
 
Back
Top