P: n/a

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 9digit
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 9digit numbers.
I hope you understand my problem. I am new in advance filtering.
Thanks in advance.  
Share this Question
P: n/a

"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 9digit
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 9digit 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  
P: n/a

On Dec 5, 7:04 pm, "Keith Wilby" <h...@there.comwrote:
"Abedin" <abed...@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 9digit
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 9digit 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.  
P: n/a

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" <h...@there.comwrote:
>"Abedin" <abed...@gmail.comwrote 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 9digit
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 9digit 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.
 
P: n/a

On Dec 5, 7:14 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Wed, 5 Dec 2007 06:09:16 0800 (PST), Abedin <abed...@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" <h...@there.comwrote:
"Abedin" <abed...@gmail.comwrote 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 9digit
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 9digit 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.comHide 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.  
P: n/a

"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 9digit 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 9digit 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 rewritten table, but might
save you from having to rewrite a lot of your application....
Fred Zuckerman  
P: n/a

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 9digit
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 9digit 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.  
P: n/a

On Dec 5, 7:25 pm, Salad <o...@vinegar.comwrote:
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 9digit
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 9digit 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.  
P: n/a

On Dec 5, 6:45 pm, Abedin <abed...@gmail.comwrote:
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 9digit
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 9digit 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   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 2717
 replies: 8
 date asked: Dec 5 '07
