How to Filter Numbers

[Abedin]

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.
I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

[Keith Wilby]

"Abedin" <ab*****@gmail.comwrote in message
news:a3**********************************@w56g2000 hsf.googlegroups.com...

>I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.
I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big" number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.
www.keithwilby.com

[Abedin]

On Dec 5, 7:04 pm, "Keith Wilby" <[e-mail]:

"Abedin" <[e-mail] in message

news:a3**********************************@w56g2000 hsf.googlegroups.com...

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.

I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big" number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.www.keithwilby.com- Hide quoted text -

- Show quoted text -

Thanks alot for your help.

Can I break Big Number into small numbers and then later concatenate?
Because I would need to filter it on each individual small numbers.
For example, I might need to filter on District, Charge, Circle, or
Book Number.

Thanks Keith.

[Tom van Stiphout]

On Wed, 5 Dec 2007 06:09:16 -0800 (PST), Abedin <ab*****@gmail.com>
wrote:

There is no need for that. If you follow Keith's advice, you have the
"small numbers" sitting in 4 fields in your table. You only
concatenate for display purposes, not for calculation purposes.

-Tom.

>On Dec 5, 7:04 pm, "Keith Wilby" <[e-mail]:

>"Abedin" <[e-mail] in message

news:a3**********************************@w56g200 0hsf.googlegroups.com...

>I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.

I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big" number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.www.keithwilby.com- Hide quoted text -

- Show quoted text -

Thanks alot for your help.

Can I break Big Number into small numbers and then later concatenate?
Because I would need to filter it on each individual small numbers.
For example, I might need to filter on District, Charge, Circle, or
Book Number.

Thanks Keith.

[Abedin]

On Dec 5, 7:14 pm, Tom van Stiphout <[e-mail]:

On Wed, 5 Dec 2007 06:09:16 -0800 (PST), Abedin <[e-mail]>
wrote:

There is no need for that. If you follow Keith's advice, you have the
"small numbers" sitting in 4 fields in your table. You only
concatenate for display purposes, not for calculation purposes.

-Tom.

On Dec 5, 7:04 pm, "Keith Wilby" <[e-mail]:

"Abedin" <[e-mail] in message

>news:a3**********************************@w56g200 0hsf.googlegroups.com...

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.

I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big" number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.www.keithwilby.com-Hide quoted text -

- Show quoted text -

Thanks alot for your help.

Can I break Big Number into small numbers and then later concatenate?
Because I would need to filter it on each individual small numbers.
For example, I might need to filter on District, Charge, Circle, or
Book Number.

Thanks Keith.- Hide quoted text -

- Show quoted text -

Got it. Thank you guys. Love you all.

[Fred Zuckerman]

"Keith Wilby" <he**@there.comwrote in message
news:47**********@glkas0286.greenlnk.net...

"Abedin" <ab*****@gmail.comwrote in message
news:a3**********************************@w56g2000 hsf.googlegroups.com...

>>I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.
I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big"
number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.
www.keithwilby.com

I agree with Keith, however, couldn't you also use a query to separate the
"big" number into a normalized split?

SELECT BigNumber, _
Mid([BigNumber],1,3) AS District, _
Mid([BigNumber],4,2) AS Charge, _
Mid([BigNumber],6,2) AS Circle, _
Mid([BigNumber],8,2) AS Book _
FROM OrigTable;

You could then link this query to OrigTable to create a new query to use.
This preserves your original structure and provides the new flexibility for
easy filtering. It might not be as fast as a re-written table, but might
save you from having to re-write a lot of your application....
Fred Zuckerman

[Salad]

Abedin wrote:

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.
I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

If you ever needed to split the 9 digit code, (and the field name is
Code) in the query builder you can do something like
District : Left(Code,3)
Charge : Mid(Code,4,2)
Circle : Mid(Code,6,2)
Book : Mid(Code,8)
This will create columns with those 4 column names. You can then filter
on them individually if you want to.

If you have a form and you need just those with district, charge, and
circle, you could use the word Like
Select * From Table Where _
Code Like '" & Forms!Main!District & _
Forms!Main!Charge
Forms!Main!Circle.
This is basically saying select all records where Code begins with 1110204.

Now, lets say you have a continuous form with a text box named "Search"
for entering your 9 digit code. When you press Enter, the AfterUpdate
event of the field could be
Me.Filter = "Code = '" & Me.Search & "'" 'stored as string
or
Me.Filter = "Code = " & Me.Search 'stored as number
Me.FilterOn = True

There's many ways to go about this. Depends on what you are doing.

[Abedin]

On Dec 5, 7:25 pm, Salad <[e-mail]:

Abedin wrote:

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.

I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

If you ever needed to split the 9 digit code, (and the field name is
Code) in the query builder you can do something like
District : Left(Code,3)
Charge : Mid(Code,4,2)
Circle : Mid(Code,6,2)
Book : Mid(Code,8)
This will create columns with those 4 column names. You can then filter
on them individually if you want to.

If you have a form and you need just those with district, charge, and
circle, you could use the word Like
Select * From Table Where _
Code Like '" & Forms!Main!District & _
Forms!Main!Charge
Forms!Main!Circle.
This is basically saying select all records where Code begins with 1110204.

Now, lets say you have a continuous form with a text box named "Search"
for entering your 9 digit code. When you press Enter, the AfterUpdate
event of the field could be
Me.Filter = "Code = '" & Me.Search & "'" 'stored as string
or
Me.Filter = "Code = " & Me.Search 'stored as number
Me.FilterOn = True

There's many ways to go about this. Depends on what you are doing.- Hide quoted text -

- Show quoted text -

Wow, this is all useful information. I will definitely use it. Thanks
again y'all.

[1983.yadav]

On Dec 5, 6:45 pm, Abedin <[e-mail]:

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.

I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

Hi...

Use substring function of Sql, with starting index and the last index.

Syntx:-select SUBSTR(column_name,String index,length) As Name from
table.

For Ex..
SQLselect SUBSTR(meterserialno,1,3) As CODE, SUBSTR(meterserialno,
4,3) AS NAME from tb_locationmst;

COD NAM
--- ---
RSX 091
RSX 092
RSX 091
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 091
Pankaj Singh Yadav