Weird Wildcard problem with Access2000

[Chris M]

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this table
is a 'Batch Id'. This is a non-unique identifier (ie there can be more than
one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query works
fine if I delete one 'BAI' row, and starts failing again when I put one
back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

 

[Chris M]

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this table
is a 'Batch Id'. This is a non-unique identifier (ie there can be more
than one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query
works fine if I delete one 'BAI' row, and starts failing again when I put
one back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

UPDATE: If the BatchId column is NOT an indexed field then it works fine. If
I make the BatchId column Indexed [Indexed: Yes(Duplicates OK) ] then I get
the problem as described.

Chris.

 

[SusanV]

What if you change the criteria to WHERE BatchId LIKE "BAI*"?

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this
table is a 'Batch Id'. This is a non-unique identifier (ie there can be
more than one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query
works fine if I delete one 'BAI' row, and starts failing again when I put
one back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

UPDATE: If the BatchId column is NOT an indexed field then it works fine.
If I make the BatchId column Indexed [Indexed: Yes(Duplicates OK) ] then I
get the problem as described.

Chris.

 

[SHIRA]

try this

SELECT Table1.BATCHID, Left([BATCHID],3) AS x
FROM Table1
WHERE (((Left([BATCHID],3))="BAI"));

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this table
is a 'Batch Id'. This is a non-unique identifier (ie there can be more
than one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query
works fine if I delete one 'BAI' row, and starts failing again when I put
one back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

UPDATE: If the BatchId column is NOT an indexed field then it works fine. If
I make the BatchId column Indexed [Indexed: Yes(Duplicates OK) ] then I get
the problem as described.

Chris.

 

[SHIRA]

try this

SELECT Table1.BATCHID, Left([BATCHID],3) AS x
FROM Table1
WHERE (((Left([role],3))="BAI"));

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this table
is a 'Batch Id'. This is a non-unique identifier (ie there can be more
than one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query
works fine if I delete one 'BAI' row, and starts failing again when I put
one back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

UPDATE: If the BatchId column is NOT an indexed field then it works fine. If
I make the BatchId column Indexed [Indexed: Yes(Duplicates OK) ] then I get
the problem as described.

Chris.

 

[Marshall Barton]

I have a table with a few thousand records. One column/field of this table
is a 'Batch Id'. This is a non-unique identifier (ie there can be more than
one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query works
fine if I delete one 'BAI' row, and starts failing again when I put one
back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

A ? wildcard must match one character. If there's no
charater, then it doesn't match.

Try checking for both cases:

WHERE BatchId LIKE "BAI/####" OR BatchId LIKE "BAI/###"

Note that the # wildcard only matches decimal digits while ?
mtches any character.

 

[John Spencer]

I would suspect a corrupted index.

I would try the following -
--Drop the index
--Compact the database
--Add the index back in

As a test you might try creating a copy of the table and putting an index on
the field. See how that behaves.

Hi group,

Wonder if anyone has ever come across this:

I have a table with a few thousand records. One column/field of this
table is a 'Batch Id'. This is a non-unique identifier (ie there can be
more than one record in the table with the same BatchId).
The format of this is 3 letters then a slash then 3 or 4 digits, eg:
BAI/1234, BAI/776, XYZ/9999

I have a query which is basically:

SELECT * FROM MyTable
WHERE BatchId LIKE "BAI/????"

Up until today, this has worked fine, and returned the correct number of
rows. All of a sudden it has started returning 0 rows.

My investigations so far show that
There are 2501(interesting number) records that start 'BAI'. The query
works fine if I delete one 'BAI' row, and starts failing again when I put
one back.
There are other prefixes that still work that have many more records, but
'BAI' is the only one that has a mix of 3 and 4 digits in the suffix, all
the rest only have 4.

I think I can get round the problem by making all BAI codes up to four
digits (ie BAI/123 -> BAI/0123). But that will be a massive PITA as these
codes are referenced all over the place.

Anyone any idea what is going on here? More to the point, anyone got any
idea how to fix it (apart from the above solution).

Many thanks,

Chris.

UPDATE: If the BatchId column is NOT an indexed field then it works fine.
If I make the BatchId column Indexed [Indexed: Yes(Duplicates OK) ] then I
get the problem as described.

Chris.

 

[Chris M]

Thanks for all your answers.

Susan and SHIRA: Thank you. Your solutions do work, but the problem is there
are may be invalid entries in the table (eg BAI/1234567) that I want to
avoid. Hence the specified 4 wildcard characters. Also the actual query is a
little more complicated. The example is the simplest that still showed the
problem.

Marshall: Thanks for your answer, I do know how wildcard characters work.
The matching entries are DEFINATLY in the table, the problem was that the
query suddenly stopped finding them. Btw, the reason I'm using '?' rather
than '#' even though I'm trying to match decimal digits is that the query is
in reality being run through Jet/ADO. (In fact the wildcard I'm actually
using is '_' but my example showed the same symptoms.) Don't believe there
is an equivilent to '#' in ADO?

John: Saving the best to last. You were spot on mate! Dropped the index,
compacted the DB, re-added the index. All is now working perfectly again.
THANK YOU!
Strange thing is, I had already tried what you also suggested and created a
copy of the table. I'm sure it showed the same problem when I added the
index, but maybe it was just getting late...?

Regards, and thanks again

Chris.

 

[peregenem]

there
are may be invalid entries in the table (eg BAI/1234567) that I want to
avoid.

CREATE TABLE MyTable (
BatchId NVARCHAR(12) NOT NULL
)
;
INSERT INTO MyTable VALUES ('BAI/1234')
;
INSERT INTO MyTable VALUES ('BAI/776')
;
INSERT INTO MyTable VALUES ('XYZ/9999')
;
INSERT INTO MyTable VALUES ('BAI/1234567')
;
SELECT * FROM MyTable
WHERE BatchId LIKE 'BAI/[0-9][0-9][0-9]'
OR BatchId LIKE 'BAI/[0-9][0-9][0-9][0-9]'
;

 

[Chris M]

there
are may be invalid entries in the table (eg BAI/1234567) that I want to
avoid.

CREATE TABLE MyTable (
BatchId NVARCHAR(12) NOT NULL
)
;
INSERT INTO MyTable VALUES ('BAI/1234')
;
INSERT INTO MyTable VALUES ('BAI/776')
;
INSERT INTO MyTable VALUES ('XYZ/9999')
;
INSERT INTO MyTable VALUES ('BAI/1234567')
;
SELECT * FROM MyTable
WHERE BatchId LIKE 'BAI/[0-9][0-9][0-9]'
OR BatchId LIKE 'BAI/[0-9][0-9][0-9][0-9]'
;

Thanks, yea tried that one too. When it wasn't working, that worked no
better than LIKE 'BAI/????' but now the damaged index has been fixed, it
works fine.

Chris.

 

[Marshall Barton]

Marshall: Thanks for your answer, I do know how wildcard characters work.
The matching entries are DEFINATLY in the table, the problem was that the
query suddenly stopped finding them. Btw, the reason I'm using '?' rather
than '#' even though I'm trying to match decimal digits is that the query is
in reality being run through Jet/ADO. (In fact the wildcard I'm actually
using is '_' but my example showed the same symptoms.) Don't believe there
is an equivilent to '#' in ADO?

As peregenem suggested, the equivalent is [0-9]

 

[Chris M]

Marshall: Thanks for your answer, I do know how wildcard characters work.
The matching entries are DEFINATLY in the table, the problem was that the
query suddenly stopped finding them. Btw, the reason I'm using '?' rather
than '#' even though I'm trying to match decimal digits is that the query
is
in reality being run through Jet/ADO. (In fact the wildcard I'm actually
using is '_' but my example showed the same symptoms.) Don't believe there
is an equivilent to '#' in ADO?

As peregenem suggested, the equivalent is [0-9]

Agreed, but what I meant was there is no single digit wildcard character in
ADO.

LIKE 'AB-####' looks sooo much neater than
LIKE 'AB-[0-9][0-9][0-9][0-9]'

still never mind, can't have everything

Thanks again for your help

Chris.