Number isssue

B

Bec_FS

Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
 
A

akphidelt

This is completely untested but before the right formula you have you can
check to see if the Integer of the number is great then one with something
like

IIF(Len(Int[YourNumber])=1,YourRightFormula,[YourNumber])
 
B

Bec_FS

Thanks for your response, I did try and test out your method for the single
digit numbers but I keep getting a syntax error:

NewTownship: IIF(Len(Int[Township])=1,Right("000" & [Township],3)

The expression you entered contains invalid syntax. (Then it highlights the
first Township.

Township What I want Township to look like
37 37
38 38
8 08
6 06
6.5 06.5
13.5 13.5
31.5 31.5
2.5 02.5
3.5 03.5
14.5 14.5
14 14



akphidelt said:
This is completely untested but before the right formula you have you can
check to see if the Integer of the number is great then one with something
like

IIF(Len(Int[YourNumber])=1,YourRightFormula,[YourNumber])


Bec_FS said:
Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
Click to expand...
Click to expand...
 
B

Bec_FS

I want to thank you for your response as well. Your format statement works,
however, as you can see in the table below, I only need the decimals for the
..5 townships. Any other ideas. Here is my table below. (Of course there is
much more data). Essentually and 1 or 3 digit number needs zeros, otherwise
it should leave the 2 and 4 digit numbers alone.

Township
37
38
8
6
6.5
13.5
31.5
2.5
3.5
14.5
14

What I want Township to look like
37
38
08
06
06.5
13.5
31.5
02.5
03.5
14.5
14

Karl Dewey Response
37.0
38.0
08.0
06.0
06.5
13.5
31.5
02.5
03.5
14.5
14.0




KARL DEWEY said:
Try this --
Format([YourField],"00.0")
--
KARL DEWEY
Build a little - Test a little


Bec_FS said:
Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
Click to expand...
Click to expand...
 
B

Bec_FS

I tried this, but it gives me a #Error for every field. Can someone take a
look at the syntax and see what I am doing wrong.

New Test Township:
IIf(Len([Township]=1),"0",IIf(Len([Township]=3),"0",[Township]))

Township
37
38
8
6
6.5
13.5
31.5
2.5
3.5
14.5
14

What I want Township to look like
37
38
08
06
06.5
13.5
31.5
02.5
03.5
14.5
14

New Test Township
#Error
#Error
#Error
#Error
#Error
#Error
#Error
#Error
#Error
#Error
#Error
 
K

KARL DEWEY

Try this ---

IIf(Right(Format([YourField],"00.#"),1)=".",Left(Format([YourField],"00.#"),Len(Format([YourField],"00.#"))-1),Format([YourField],"00.#"))

--
KARL DEWEY
Build a little - Test a little


Bec_FS said:
I want to thank you for your response as well. Your format statement works,
however, as you can see in the table below, I only need the decimals for the
.5 townships. Any other ideas. Here is my table below. (Of course there is
much more data). Essentually and 1 or 3 digit number needs zeros, otherwise
it should leave the 2 and 4 digit numbers alone.

Township
37
38
8
6
6.5
13.5
31.5
2.5
3.5
14.5
14

What I want Township to look like
37
38
08
06
06.5
13.5
31.5
02.5
03.5
14.5
14

Karl Dewey Response
37.0
38.0
08.0
06.0
06.5
13.5
31.5
02.5
03.5
14.5
14.0




KARL DEWEY said:
Try this --
Format([YourField],"00.0")
--
KARL DEWEY
Build a little - Test a little


Bec_FS said:
Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
Click to expand...
Click to expand...
Click to expand...
 
B

Bec_FS

That worked great, I would have never come up with that. Thank you.

KARL DEWEY said:
Try this ---

IIf(Right(Format([YourField],"00.#"),1)=".",Left(Format([YourField],"00.#"),Len(Format([YourField],"00.#"))-1),Format([YourField],"00.#"))

--
KARL DEWEY
Build a little - Test a little


Bec_FS said:
I want to thank you for your response as well. Your format statement works,
however, as you can see in the table below, I only need the decimals for the
.5 townships. Any other ideas. Here is my table below. (Of course there is
much more data). Essentually and 1 or 3 digit number needs zeros, otherwise
it should leave the 2 and 4 digit numbers alone.

Township
37
38
8
6
6.5
13.5
31.5
2.5
3.5
14.5
14

What I want Township to look like
37
38
08
06
06.5
13.5
31.5
02.5
03.5
14.5
14

Karl Dewey Response
37.0
38.0
08.0
06.0
06.5
13.5
31.5
02.5
03.5
14.5
14.0




KARL DEWEY said:
Try this --
Format([YourField],"00.0")
--
KARL DEWEY
Build a little - Test a little


:

Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
Click to expand...
Click to expand...
Click to expand...
 
M

m3

Lets make it simple:

iif(len(cstr(int(YourNumber0)))=1,"0" & cstr(YourNumber),cstr(YourNumber))

See if its what you need!
 
M

m3

Sorry my typo! Here's the correct one

iif(len(cstr(int(YourNumber)))=1,"0" & cstr(YourNumber),cstr(YourNumber))

Cheers :)

m3 said:
Lets make it simple:

iif(len(cstr(int(YourNumber0)))=1,"0" & cstr(YourNumber),cstr(YourNumber))

See if its what you need!

Bec_FS said:
Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit)
then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in
front
of double digit number like 37, etc. I thought that I then might be able
to
trim of the zeros, but that creates a problem because with the single
digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP
ME
WITH THIS?
Click to expand...
Click to expand...
 
B

Beepa

I'm fairly new at this but in the field properties couldn't you just use
these settings:
decimal places = 1
output mask = 00/.0;0

Again I'm fairly young at using Access but I hope it is useful info.
 
B

Beepa

NM! I see I should test before opening my mouth. It doesn't work. Sorry if I
confounded the matter any! I will hush now! :)

Beepa said:
I'm fairly new at this but in the field properties couldn't you just use
these settings:
decimal places = 1
output mask = 00/.0;0

Again I'm fairly young at using Access but I hope it is useful info.

Bec_FS said:
Okay, I have a column within a dataset that has number as follows...
37
8
31.5
6.5
6
3
2.6
18.5
8.5
Etc. Etc.
Well, if there is a number with say 6, 6.5, 3, 4.5 etc, (single digit) then
I need it to have a zero in front of it. I tried to use: Right("0000" &
[Range Number],4), but the problem with this is that it puts zeros in front
of double digit number like 37, etc. I thought that I then might be able to
trim of the zeros, but that creates a problem because with the single digits,
and single digits with .5, I want it to keep the zeros. CAN ANY ONE HELP ME
WITH THIS?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding recognised Zeros 5
Date and Number cell formats 4
Leading Zeros; How to Keep ? 2
For John, et al. Re Combining Columns (previous post) 4
Need 7 digit number with differnt number of leading zeros 10
How do you change single digits to recognized double digits? 4
Conditional Formatting 0
Condtional Formatting 6

Top