Search Table

[GavZ]

Hi - Can anyone help?

I have a table the is regularly updated with lots of information and i need to search that table in all field for all 11 digit numbers and extract them into a form or other table.

i am using Access 2003

Thanks

 

[PaulO]

Just QUERY the field(s) with a criteria of len([fieldname])=11

If you 'step' the criteria using the 'or' criteria variant across each field then one query will find any and all records containing one or more 11 character field

 

[GavZ]

Thanks for the reply - i got it to work but i also need it to search part fields for the 11 digits only for example some fields have -

Paul mobile <01234567890>
Industry 09876543210 takes on
make 99999999999 the number

once these have been located then i could do with them being copied to another table on their own??

 

[DCrake]

Can the search criteria appear more than once in any given field?
Does the search criteria appear more than once in a number of given fields?
Does the search criteria change for any given record?
Do all above questions = Yes

 

[GavZ]

Can the search criteria appear more than once in any given field? - No
Does the search criteria appear more than once in a number of given fields? - Yes
Does the search criteria change for any given record? - Yes
Do all above questions = Yes


Does this help??

 

[DCrake]

How many fields do you need to search in?
Are they all text fields?


You could create a query and concatenate the desired fields and do a Like search on the column.

 

[GavZ]

12000 odd records and yes they are text fields. i have tried the like criteria but how do i get it to search any 11 digit number?

also ideally what i need is the query just to display the number and no other part of the field.

 

[DCrake]

No not how many Records but how many table fields?

Can you provide sample data?

 

[GavZ]

hi - i have got the criteria back that i need but i want to remove everything else in the field apart from what i searched for using the criteria - is this possible?

 

[DCrake]

What does the data look like in the first place? At the moment it's a bit like peeling an orange in your pocket.

 

[GavZ]

Here is the DB, if you look at the table you will see the data looks a mess. what i am trying to do is extract all of the phone numbers out of the table (they could be in any field or part field) with no letters symbols etc and copy them into another table so the second table will be one long set of phone numbers.

i am really struggling with this if you can be of any assistance!

 

[DCrake]

Silly question but if you manage to do this what good is a list of phone numbers to you if you do not know who they belong to?

 

[GavZ]

they are useful to me - can you help?

 

[DCrake]

If this is only going to be a one off exercise then a bit of hard work is required. However if this is going to be an ongoing event then that multiplies the work ten fold.

Which option are you looking for?

 

[GavZ]

definatley an on-going event! that was just a sample i gave you

 

[DCrake]

To be perfectly honest I would suggest seeking profesional help on this matter. The structure of the incoming data is so disjointed and denormalised it would be a nightmare to program.

 

[indianagssa]

so nice of you