446,224 Members | 1,143 Online
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

How to Filter Numbers

 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 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. Dec 5 '07 #1
8 Replies

 P: n/a "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. 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 Dec 5 '07 #2

 P: n/a On Dec 5, 7:04 pm, "Keith Wilby"

 P: n/a On Wed, 5 Dec 2007 06:09:16 -0800 (PST), Abedin 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" "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. You need to normalise your data and store each element of data in its ownfield, thus you can query each field as necessary. Your fields:DistrictChargeCircleBookNumbercan 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, orBook Number.Thanks Keith. Dec 5 '07 #4

 P: n/a On Dec 5, 7:14 pm, Tom van Stiphout 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" 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. Dec 5 '07 #5

 P: n/a "Keith Wilby" >I have 9 digits in form of "111020402". Then I have another 9 digitsin form of "111020403". I have 100,000 records of these two 9-digitnumbers. I want to filter this as follows:District: 111Charge: 02Circle: 04Book Number: 02For example, if somone asks me to filter 111020402 and 111020403, thenI need to filter in terms of Book Number and tell the person how manyrecords 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 Dec 5 '07 #6

 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 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. Dec 5 '07 #7

 P: n/a On Dec 5, 7:25 pm, Salad

 P: n/a On Dec 5, 6:45 pm, Abedin