Wildcard for text string

J

Jeff C

Trying to use delete query to clean up imported data. One field holds a
unique identifier that is always 3 letters followed by 4 numbers, no spaces,
field properties set to text.

Is there I way to style a "Not Like" "Wildcard" so that the identifier will
remain and everything else be deleted?

Thank you
 
K

Ken Snell [MVP]

You want to delete the data from all the fields except the identifier field?
Or you want to delete all records except those that contain the identifier?

More details, please.
 
J

Jeff C

Sorry, I wasn't very clear at all.

I may not be able to do anything with the file after all. Each record has
fields over several rows.
Field1Column1 is a unique logon name, field2Column2 is the last name,
field3Column3 is the first name, each record has many rows. The row directly
under the row holding the names has a header in the field2 column "ID:" then
the ""???####" Identifier sits in column4. Something like this

J3E.TGH Smith John
ID: STY3546


Then there are rows of unusable and un-needed datamaking up columns 2, 3,
and 4., before the next record.

I would love to be able to move the STY3546 up into column 4 on the same row
and the rest of the name and login info.

Thanks for any help
 
J

John Vinson

Trying to use delete query to clean up imported data. One field holds a
unique identifier that is always 3 letters followed by 4 numbers, no spaces,
field properties set to text.

Is there I way to style a "Not Like" "Wildcard" so that the identifier will
remain and everything else be deleted?

Thank you
Click to expand...

NOT LIKE "[A-Z][A-Z][A-Z]####"

should do it... try it first of course, and back up your database
before running this or any other delete query!

John W. Vinson[MVP]
 
K

Ken Snell [MVP]

Your example doesn't give me enough data to give you a "solid" answer, but
it sounds as if the data are not well structured into a single record being
one row in the text file.

You probably would need a customized solution via VBA code, where you'd open
the text file from code, read the data and write them into a recordset so
that you control what goes where, and then you could probably clean up the
data from there.
 
J

Jeff C

Thank you both. John, your solution worked. The other solution that matched
the identifier was "???####".

As for the structure of the imported data, I think a programming expert
could do it, I certainly cannot come up with a solution.

Thanks again
 
J

John Vinson

Thank you both. John, your solution worked. The other solution that matched
the identifier was "???####".
Click to expand...

That's less exclusive... ? matches ANY character, so 1234444 would be
a hit.
As for the structure of the imported data, I think a programming expert
could do it, I certainly cannot come up with a solution.
Click to expand...

Been there... done that... know the feeling!

John W. Vinson[MVP]
 
Top