String Query

[Guest]

Please could someone assist me.

I am sent some data, which i need to link in on existing access table. The
problem is the data quality of the manual data, the field I link on is an
alpha numeric code (e.g. A111111) this though, can contain many typos.

I have developed several queries to isolate these typos, in order to send
back to be corrected, wrong number of characters etc. The one issue I have
is if the string contains a character in the wrong place e.g A11G111. This
rogue character can be anywhere in the string, after the inital character.

Do i need to improvise some string function?

much appreciate the assistance.

 

[Gary Walter]

I am sent some data, which i need to link in on existing access table.
The
problem is the data quality of the manual data, the field I link on is an
alpha numeric code (e.g. A111111) this though, can contain many typos.

I have developed several queries to isolate these typos, in order to send
back to be corrected, wrong number of characters etc. The one issue I
have
is if the string contains a character in the wrong place e.g A11G111.
This
rogue character can be anywhere in the string, after the inital character.

Hi Nigel,

I am not showing a response to your question,
so...if you have not yet received a response...

It sounds like you want


WHERE [somefield] NOT LIKE [A-Z]######

this would return every record where "somefield"
does not start with a capital letter followed by
6 numbers.

good luck,

gary

 

[Gary Walter]

of course your wildcard pattern needs to be wrapped
in quotes...

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

I am sent some data, which i need to link in on existing access table.
The
problem is the data quality of the manual data, the field I link on is an
alpha numeric code (e.g. A111111) this though, can contain many typos.

I have developed several queries to isolate these typos, in order to send
back to be corrected, wrong number of characters etc. The one issue I
have
is if the string contains a character in the wrong place e.g A11G111.
This
rogue character can be anywhere in the string, after the inital
character.

Hi Nigel,

I am not showing a response to your question,
so...if you have not yet received a response...

It sounds like you want


WHERE [somefield] NOT LIKE [A-Z]######

this would return every record where "somefield"
does not start with a capital letter followed by
6 numbers.

good luck,

gary

 

[Guest]

many thanks, great help!

of course your wildcard pattern needs to be wrapped
in quotes...

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

I am sent some data, which i need to link in on existing access table.
The
problem is the data quality of the manual data, the field I link on is an
alpha numeric code (e.g. A111111) this though, can contain many typos.

I have developed several queries to isolate these typos, in order to send
back to be corrected, wrong number of characters etc. The one issue I
have
is if the string contains a character in the wrong place e.g A11G111.
This
rogue character can be anywhere in the string, after the inital
character.

Hi Nigel,

I am not showing a response to your question,
so...if you have not yet received a response...

It sounds like you want


WHERE [somefield] NOT LIKE [A-Z]######

this would return every record where "somefield"
does not start with a capital letter followed by
6 numbers.

good luck,

gary