Group field up to a decimal

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that has a field [QuesNo]. The format is 1.1.1 up to
10.1.25. I am having a couple of problems:

First I want to have a subset so I can group the categories. So I want to
take all the numbers up to the second decimal. The category would be 1.1,
1.2, 1.3...10.1, 10.2, etc. My first question -- how do I tell Access that I
want all the characters up to and not including the second "."?

My second question is how do I have my report sort so that 1.1.10 does not
come before 1.2.1?

Any help would be appreciated.
 
About the fiest question, add another field in the record source of the report
Select Field1, Field2 , left(Field3,instr(Field3,".")-1) as FieldNew From
TableName

The FieldNew field will apply a new field that display the number up to the
first dot

About the second question, it make sense that it will apear first, how do
you want it to sort?
 
You'll find a lot of your problems disappear if, instead of one text field,
you store the three parts of your 'number' in three integer fields. You can
put them back together again at any time with a simple expression: [Field1]
& "." & [Field2] & "." & [Field3]
 
I put the left... in the query. It only returns just one character, which by
the way, I am going to use. But I also need all the numbers up until the
second decimal. So the number currently is 1.1.1, 1.1.2, 1.1.3...10.1.1,
10.1.2, etc. I need to have just 1.2, 1.3...10.1, 10.2 etc.

Thanks for the quick response!

Ofer said:
About the fiest question, add another field in the record source of the report
Select Field1, Field2 , left(Field3,instr(Field3,".")-1) as FieldNew From
TableName

The FieldNew field will apply a new field that display the number up to the
first dot

About the second question, it make sense that it will apear first, how do
you want it to sort?

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



jhicsupt said:
I have a report that has a field [QuesNo]. The format is 1.1.1 up to
10.1.25. I am having a couple of problems:

First I want to have a subset so I can group the categories. So I want to
take all the numbers up to the second decimal. The category would be 1.1,
1.2, 1.3...10.1, 10.2, etc. My first question -- how do I tell Access that I
want all the characters up to and not including the second "."?

My second question is how do I have my report sort so that 1.1.10 does not
come before 1.2.1?

Any help would be appreciated.
Click to expand...
Click to expand...
 
First question, use the Instr function and Left function to get the value
Left(QuesNo,Instr(Instr(1,QuesNo,".")+1,QuesNo,".")-1)

That should work unless you have Quesno, that don't have two periods or that are
completely blank

Second question, this can be a tough one to solve. It can be done with instr
and left and mid functions and generating multiple columns to sort on.

First Sort Column
Val(Left(Quesno,Instr(1,QuesNo,".")))

Second Sort Column is only a bit more complicated.
Val(Mid(Quesno,Instr(1,Quesno,".")+1,2))

Third Sort Column (Assumes Access 2000 with latest service packs or later)
Val(Mid(Quesno,InstrRev(Quesno,".",-1,1)+1))
 
The number already exists as 1.1.10, so I don't know how to break it out.

In addition, when I am grouping, I am doing:

1.1
1.1.1
1.1.2
...
1.1.10

What is happening is 1.1.10 is coming out before 1.1.2. This problem still
exists also.

Brendan Reynolds said:
You'll find a lot of your problems disappear if, instead of one text field,
you store the three parts of your 'number' in three integer fields. You can
put them back together again at any time with a simple expression: [Field1]
& "." & [Field2] & "." & [Field3]

--
Brendan Reynolds


jhicsupt said:
I have a report that has a field [QuesNo]. The format is 1.1.1 up to
10.1.25. I am having a couple of problems:

First I want to have a subset so I can group the categories. So I want to
take all the numbers up to the second decimal. The category would be 1.1,
1.2, 1.3...10.1, 10.2, etc. My first question -- how do I tell Access
that I
want all the characters up to and not including the second "."?

My second question is how do I have my report sort so that 1.1.10 does not
come before 1.2.1?

Any help would be appreciated.
Click to expand...
Click to expand...
 
Back
Top