Trouble with Len Function

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

Guest

I have searched for this and have not found a solution. I am sorry if this is
a double post. I am trying to group a report using the first 1 or 2 numbers
in a field. The field [CMG] can be 3 or 4 digits. If it is 3, I want the
first digit, 4, the first 2 digits..
I am working with 2000 Ver. The code I have so far is as follows

=IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))

This returns a value but when I place it in the report group header, the
order is all over the place..
Thanks for any help you can give....
 
Try using a query as the report's record source, and add to the query a
field containing your expression:
ShortCMG: IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))
then group by ShortCMG.
 
Thanks Bruce,
That seems to work BUT if I may ask one more thing...
I am getting groups now like 10,11,12,13,14 and then it goes to 1,2,3,4,
is there a way to get the grouping to go 1,2,3,......9,10,11,12

Thanks again for your help.....
--
Ed
Learning as I go.
Helping others as I go.
Making better Databases


BruceM said:
Try using a query as the report's record source, and add to the query a
field containing your expression:
ShortCMG: IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))
then group by ShortCMG.

Edward said:
I have searched for this and have not found a solution. I am sorry if this
is
a double post. I am trying to group a report using the first 1 or 2
numbers
in a field. The field [CMG] can be 3 or 4 digits. If it is 3, I want the
first digit, 4, the first 2 digits..
I am working with 2000 Ver. The code I have so far is as follows

=IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))

This returns a value but when I place it in the report group header, the
order is all over the place..
Thanks for any help you can give....

--
Ed
Learning as I go.
Helping others as I go.
Making better Databases
Click to expand...
Click to expand...
 
Thanks Bruce,
That seems to work BUT if I may ask one more thing...
I am getting groups now like 10,11,12,13,14 and then it goes to 1,2,3,4,
is there a way to get the grouping to go 1,2,3,......9,10,11,12

Thanks again for your help.....
Click to expand...

Change the expression to
Val(IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1)))

Does that help?
 
Is CMG a string field consisting of numbers or is it a number field? You
can try some integer arithmetic.

NumberField
[CMG]\100

String field
Val([CMG])\100

And the \ is correct although in this case / might work just as well.
 
Thanks all for all the input

Val(IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))) did not work, too complex
for the querry..

[CMG] is a number field

John, Where would I enter [CMG]\100 into the equation ?

Thanks again
--
Ed
Learning as I go.
Helping others as I go.
Making better Databases


John Spencer said:
Is CMG a string field consisting of numbers or is it a number field? You
can try some integer arithmetic.

NumberField
[CMG]\100

String field
Val([CMG])\100

And the \ is correct although in this case / might work just as well.

Edward said:
I have searched for this and have not found a solution. I am sorry if this
is
a double post. I am trying to group a report using the first 1 or 2
numbers
in a field. The field [CMG] can be 3 or 4 digits. If it is 3, I want the
first digit, 4, the first 2 digits..
I am working with 2000 Ver. The code I have so far is as follows

=IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))

This returns a value but when I place it in the report group header, the
order is all over the place..
Thanks for any help you can give....

--
Ed
Learning as I go.
Helping others as I go.
Making better Databases
Click to expand...
Click to expand...
 
Thanks all for all the input

Val(IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))) did not work, too complex
for the querry..

[CMG] is a number field

John, Where would I enter [CMG]\100 into the equation ?

Thanks again
Click to expand...

Regarding your sentence:
Val(IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))) did not work, too
complex for the querry..

Then you have something else going on because I just tried it and it
worked fine.
 
Well if you want to group by it I would either put it in the query as a
calculated field or try using it in the sorting and grouping dialog as

=[CMG]\100


Edward said:
Thanks all for all the input

Val(IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))) did not work, too
complex
for the querry..

[CMG] is a number field

John, Where would I enter [CMG]\100 into the equation ?

Thanks again
--
Ed
Learning as I go.
Helping others as I go.
Making better Databases


John Spencer said:
Is CMG a string field consisting of numbers or is it a number field? You
can try some integer arithmetic.

NumberField
[CMG]\100

String field
Val([CMG])\100

And the \ is correct although in this case / might work just as well.

Edward said:
I have searched for this and have not found a solution. I am sorry if
this
is
a double post. I am trying to group a report using the first 1 or 2
numbers
in a field. The field [CMG] can be 3 or 4 digits. If it is 3, I want
the
first digit, 4, the first 2 digits..
I am working with 2000 Ver. The code I have so far is as follows

=IIf(Len([CMG])>3,Left([CMG],2),Left([CMG],1))

This returns a value but when I place it in the report group header,
the
order is all over the place..
Thanks for any help you can give....

--
Ed
Learning as I go.
Helping others as I go.
Making better Databases
Click to expand...
Click to expand...
Click to expand...
 
Back
Top