Weird Wildcard problem i Access

[Guest]

I'm working with an Access database and one of the fields contain codes like
this:

A03AA01
A03AB01


and so on

I would like to make a query where I select all records that contain A03A

So I write:

Like "A03A*"

But I get no records despite the fact that there are several records which
fits this template.

If I write:

Like "A03*"

I get the ones listed above

Also if I write

Like "A03AA*"

will I get the first ones.

The rule seems to be that if using * as a wildcard it only works if:

1. * represents a number after a letter (A03AA* = A03AA01)
2. * represents a letter after a number (A03* = A03AA01)

Now to the extra weird part:

Some of the entries work just fine with wildcard.

And it gets weirder:

If I take an original list of all the different possible values of this
field and link to the aforementioned field Access links all posts and has no
problems recognizing that A03AA01 in one table is equal to A03AA01 in another
table.

Now for the superweird part.

Wildcards works fine if I use it in the original list.

So this means that A03AA01 can't be identified in one table by A03A*
But in another table, which contains elements that (according to Access) are
EQUAL to A03AA01, A03AA01 can easily be identified by the wildcard A03A*.



I can of course solve the problem simply by linking the original list to the
field and make the query on the field in the original list instead of on the
field that behaves strangely. But it would be nice to understand the problem.

Both the original list and the problematic table have been exported to Excel
and later reimported into Access. I assume that some problems have arisen
there but I can't find anything that seperates those two fields that
apparently are equal but behaves differently to wildcards.

Any suggestions are welcome

 

[Guest]

I have tried the left$ command and it works fine with my data so it seems
that there is a bug in Access when it comes to using wildcard in simple
quesries.

 

[Brian Bastl]

try ==> Like "*" & "A03" & "*" for the criteria

Brian

 

[Guest]

Like "A03A*"

and

Like "A03A" & "*"

and

Like "A03" & "A*"

don't work

left$(field,4) = "A03A" works just fine.

The weird thing is that on the original data the wildcard works but access
doesn't see any difference between the two sets of data. If I form links
between the two fields access claimes that they are 100% identical.

 

[John Vinson]

will I get the first ones.

The rule seems to be that if using * as a wildcard it only works if:

1. * represents a number after a letter (A03AA* = A03AA01)
2. * represents a letter after a number (A03* = A03AA01)

Now to the extra weird part:

Some of the entries work just fine with wildcard.

And it gets weirder:

If I take an original list of all the different possible values of this
field and link to the aforementioned field Access links all posts and has no
problems recognizing that A03AA01 in one table is equal to A03AA01 in another
table.

Now for the superweird part.

Wildcards works fine if I use it in the original list.

So this means that A03AA01 can't be identified in one table by A03A*
But in another table, which contains elements that (according to Access) are
EQUAL to A03AA01, A03AA01 can easily be identified by the wildcard A03A*.

Hmmm... what version of Access are you using? Perhaps with all the
exporting and importing, some indexes are messed up. Try opening the
table in design view; removing any index on this field (turn off its
Primary Key property if it's the PK); compact the database; and
reapply the index.

What makes me suspicious is that A03A is a perfectly valid hexadecimal
number - I wonder if it somehow got interpreted as a hex number rather
than a string at some point!

John W. Vinson[MVP]

 

[Guest]

I use Access 2002 SP3

I'm not sure what you want me to do. I have a screenshot of the table which
is causing the problems:

http://www.euphonium.dk/access.gif

It's in Danish.

Tekst = text
Ja = yes
Nej = no
Intet kontrolelement = no control element
Ingen = none

I have tried to export the table to excel and seve it as a text file and
reimport it but the problem remains.

The field that is causing problems is the ATCC field.

 

[Peter Doering]

I use Access 2002 SP3

I'm not sure what you want me to do. I have a screenshot of the table which
is causing the problems:

http://www.euphonium.dk/access.gif

It's in Danish.

^^^^^^
Change the collating order to whatever in your version comes closest to
English (General, Western European, ...?). You'll find this is in Tools -
Options, tab General, "New database sort order". Then compact/repair the
database and repeat your tests.

Peter

 

[Guest]

Ok I did that.

Fileformat is Access 2002.Database sort order: Genral.

Database has been compacted/repaired.

Problem remains the same.

Like "HC03A*" yields no records

Like "HC03AA*" yields 3 records

 

[John Vinson]

I use Access 2002 SP3

I'm not sure what you want me to do. I have a screenshot of the table which
is causing the problems:

http://www.euphonium.dk/access.gif

It's in Danish.

Tekst = text
Ja = yes
Nej = no
Intet kontrolelement = no control element
Ingen = none

I have tried to export the table to excel and seve it as a text file and
reimport it but the problem remains.

The field that is causing problems is the ATCC field.

Very strange! I'm not certain that it might not be a localization
error (i.e. in the Danish version of Access), but I can't reproduce in
my installation of (American English) A2003. A table with two fields,
ID autonumber, and ATCC text, with values

ATCC
A03AA01
A03AA02
A03AA03
A03BA00
A03BA01
A03BA02

finds the expected six records searching for "A03*", and three records
using "A03A*" or "A03AA*". I tried it with the field unindexed, and
again with a nonunique index - no difference.

One unlikely but just possible issue; if you have Name Autocorrect
turned on, try turning it off. It's *very* buggy by all accounts.

John W. Vinson[MVP]

 

[Guest]

For some reason it now works in my current database. If I open an old copy it
still doesn't work.

I have no clue what fixed it. If I export the non working tabel and import
it in a new clean database it works fine too.

I have tried to export the part that doesn't work so you could have a look
at it but since it now works fine when I reimport there is no point in doing
that and since the nonworking datase contains sensitive data I can't make the
it public .

Anyway thanks for all your suggestions. They worked - some how. I just don't
know which one did it.

Kristian