Query on text field with multiple spaces within the string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I imported a FoxPro db file (don't know what version - came as a dbf & cdx)
using ODBC driver into Access 2003. The table contains a text field that
often contains more than 1 space within the text string. Queries on this data
returns nothing. If I edit a record to contain only one space it works.

A simple example ("?" is visual placeholder for the blank spaces):
1??A
1?A

query with this criteria: like "*[A-Z]" will return only the 2nd record.

After 2 days of hairpulling, I found that if I export the imported FoxPro
table as text, then reimport it, I do not have this problem

My question: what is the value being stored as a space in the originally
imported text field that makes the original query fail?

I do not have a copy of FoxPro to work with this data.

Thanks,
Andy
 
Hi Andy,

My guess is that the misbehaving "spaces" are in fact nulls, Chr(0). But
you can find out for yourself by creating a SELECT query that returns
one or two records from this table, and including in it a calculated
field like this:
MysteryChar: Asc(Mid([X], Y, 1))
where X is the name of the text field in question, and Y the position of
the "space" in that field.

For a null, this should return 0; for a real space, 32.

Once you know what the "spaces" are, you can get rid of them by running
a query that updates the field to
Replace([X], Chr(Z), " ")
where X is the field name and Z the character code you found in the
previous step.

I imported a FoxPro db file (don't know what version - came as a dbf & cdx)
using ODBC driver into Access 2003. The table contains a text field that
often contains more than 1 space within the text string. Queries on this data
returns nothing. If I edit a record to contain only one space it works.

A simple example ("?" is visual placeholder for the blank spaces):
1??A
1?A

query with this criteria: like "*[A-Z]" will return only the 2nd record.

After 2 days of hairpulling, I found that if I export the imported FoxPro
table as text, then reimport it, I do not have this problem

My question: what is the value being stored as a space in the originally
imported text field that makes the original query fail?

I do not have a copy of FoxPro to work with this data.

Thanks,
Andy
Click to expand...
 
I suspected it might have something to do with this, but did not know how to
test for it.

In any case, this did not return any useful info. I tested for position 6 &
position 7 separately and both values returned as 32. I tried editing a field
& replacing the spaces, but same result. So it seems null value is not
involved.

Any other ideas?

Andy

John Nurick said:
Hi Andy,

My guess is that the misbehaving "spaces" are in fact nulls, Chr(0). But
you can find out for yourself by creating a SELECT query that returns
one or two records from this table, and including in it a calculated
field like this:
MysteryChar: Asc(Mid([X], Y, 1))
where X is the name of the text field in question, and Y the position of
the "space" in that field.

For a null, this should return 0; for a real space, 32.

Once you know what the "spaces" are, you can get rid of them by running
a query that updates the field to
Replace([X], Chr(Z), " ")
where X is the field name and Z the character code you found in the
previous step.

I imported a FoxPro db file (don't know what version - came as a dbf & cdx)
using ODBC driver into Access 2003. The table contains a text field that
often contains more than 1 space within the text string. Queries on this data
returns nothing. If I edit a record to contain only one space it works.

A simple example ("?" is visual placeholder for the blank spaces):
1??A
1?A

query with this criteria: like "*[A-Z]" will return only the 2nd record.

After 2 days of hairpulling, I found that if I export the imported FoxPro
table as text, then reimport it, I do not have this problem

My question: what is the value being stored as a space in the originally
imported text field that makes the original query fail?

I do not have a copy of FoxPro to work with this data.

Thanks,
Andy
Click to expand...
Click to expand...
 
If Asc() says those spaces are Chr(32) then either they are kosher or
you were testing the wrong characters. The next thing I'd do would be to
open a recordset containing just one record and one field - one that you
know to misbehave, and then check every byte in the field. E.g. in the
Immediate window do something like this. You may well find some nulls or
other invisible characters hidden in there.

Set R = CurrentDB.Openrecordset("SELECT TheField FROM TheTable WHERE
ID=123";)
N = Len(R.Fields(0).Value)
For j = 1 to N: ? j, Asc(Mid(R.Fields(0).Value)) : Next

I suspected it might have something to do with this, but did not know how to
test for it.

In any case, this did not return any useful info. I tested for position 6 &
position 7 separately and both values returned as 32. I tried editing a field
& replacing the spaces, but same result. So it seems null value is not
involved.

Any other ideas?

Andy

John Nurick said:
Hi Andy,

My guess is that the misbehaving "spaces" are in fact nulls, Chr(0). But
you can find out for yourself by creating a SELECT query that returns
one or two records from this table, and including in it a calculated
field like this:
MysteryChar: Asc(Mid([X], Y, 1))
where X is the name of the text field in question, and Y the position of
the "space" in that field.

For a null, this should return 0; for a real space, 32.

Once you know what the "spaces" are, you can get rid of them by running
a query that updates the field to
Replace([X], Chr(Z), " ")
where X is the field name and Z the character code you found in the
previous step.

I imported a FoxPro db file (don't know what version - came as a dbf & cdx)
using ODBC driver into Access 2003. The table contains a text field that
often contains more than 1 space within the text string. Queries on this data
returns nothing. If I edit a record to contain only one space it works.

A simple example ("?" is visual placeholder for the blank spaces):
1??A
1?A

query with this criteria: like "*[A-Z]" will return only the 2nd record.

After 2 days of hairpulling, I found that if I export the imported FoxPro
table as text, then reimport it, I do not have this problem

My question: what is the value being stored as a space in the originally
imported text field that makes the original query fail?

I do not have a copy of FoxPro to work with this data.

Thanks,
Andy
Click to expand...
Click to expand...
Click to expand...
 
I'm fairly sure I tested the right characters. In fact, I stepped thru all of
them with the query before & after the 2 spaces (32) and verified the char
value with the decimal value for each position.

BTW, if there WERE null values (dec 00) within the field, wouldn't Access
table view NOT display these as if they were spaces? In other words, wouldn't
it squeeze up so that it appeared there were no blank spaces?

Thanks for the code suggestion, but this is stepping into waters over my
head. Though I can open an immediate window, I have no idea how to run it.

Thanks again, it will remain a mystery.

In any case, I worked around the problem by exporting the entire table as
text, then re-importing it. Though this took about 3 hours, it did fix the
problem.

Andy

John Nurick said:
If Asc() says those spaces are Chr(32) then either they are kosher or
you were testing the wrong characters. The next thing I'd do would be to
open a recordset containing just one record and one field - one that you
know to misbehave, and then check every byte in the field. E.g. in the
Immediate window do something like this. You may well find some nulls or
other invisible characters hidden in there.

Set R = CurrentDB.Openrecordset("SELECT TheField FROM TheTable WHERE
ID=123";)
N = Len(R.Fields(0).Value)
For j = 1 to N: ? j, Asc(Mid(R.Fields(0).Value)) : Next

I suspected it might have something to do with this, but did not know how to
test for it.

In any case, this did not return any useful info. I tested for position 6 &
position 7 separately and both values returned as 32. I tried editing a field
& replacing the spaces, but same result. So it seems null value is not
involved.

Any other ideas?

Andy

John Nurick said:
Hi Andy,

My guess is that the misbehaving "spaces" are in fact nulls, Chr(0). But
you can find out for yourself by creating a SELECT query that returns
one or two records from this table, and including in it a calculated
field like this:
MysteryChar: Asc(Mid([X], Y, 1))
where X is the name of the text field in question, and Y the position of
the "space" in that field.

For a null, this should return 0; for a real space, 32.

Once you know what the "spaces" are, you can get rid of them by running
a query that updates the field to
Replace([X], Chr(Z), " ")
where X is the field name and Z the character code you found in the
previous step.

On Wed, 4 Jan 2006 08:31:04 -0800, "AndyB"

I imported a FoxPro db file (don't know what version - came as a dbf & cdx)
using ODBC driver into Access 2003. The table contains a text field that
often contains more than 1 space within the text string. Queries on this data
returns nothing. If I edit a record to contain only one space it works.

A simple example ("?" is visual placeholder for the blank spaces):
1??A
1?A

query with this criteria: like "*[A-Z]" will return only the 2nd record.

After 2 days of hairpulling, I found that if I export the imported FoxPro
table as text, then reimport it, I do not have this problem

My question: what is the value being stored as a space in the originally
imported text field that makes the original query fail?

I do not have a copy of FoxPro to work with this data.

Thanks,
Andy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top