Records not matching with Criteria (1 Viewer)

DataAnalyst86 · Dec 12, 2017

Hello All,

I'm running a very simple query where I only want the preferred address for a particular record. There is a Short Text field named "Preferred" populated with either "Y" or "N"

My query is as follows

Select *
FROM Table
WHERE Preferred = "Y";
---------------------------------------------
Why am I getting records in my results set where the Preferred = N?

I even tried
Select *
FROM Table
WHERE Preferred <>"N";
----------------------------------
Yet Preferred = N is still included in the results set. I want these excluded. How do I do so?

Thanks in advance for any help.

plog · Dec 12, 2017

If you want Preferred to contain only 2 values, it should be a Yes/No data field. Sounds like its text.

"N " <> "N"
"N\t" <> "N"
"N\n" <> "N"

You could have a space, a tab or a return character in that string field. Yes/No fields won't accomodate "hidden" characters. Add a calcualted field to that last query:

PLength: Len([Preferred])

Then see what the length is of those seemingly "N" values. Greater than 1 and that means you have a hidden character.

Ranman256 · Dec 12, 2017

is [Preferred] as string field or a boolean Y/N field?

DataAnalyst86 · Dec 12, 2017

Ranman256 said:
is [Preferred] as string field or a boolean Y/N field?

[Preferred] is a Short Text field. So it's a string.

DataAnalyst86 · Dec 12, 2017

plog said:
If you want Preferred to contain only 2 values, it should be a Yes/No data field. Sounds like its text.

"N " <> "N"
"N\t" <> "N"
"N\n" <> "N"

You could have a space, a tab or a return character in that string field. Yes/No fields won't accomodate "hidden" characters. Add a calcualted field to that last query:

PLength: Len([Preferred])

Then see what the length is of those seemingly "N" values. Greater than 1 and that means you have a hidden character.

----------------------------------------------------------

I added that calculated field and there are no values greater than 1. So much for the hidden character theory? Thank you for the solution. I don't understand why this is occurring.

plog · Dec 12, 2017

Do you have additional criteria in the actual query? Can you post the full actual SQL? Can you post your database?

DataAnalyst86 · Dec 12, 2017

No additional Criteria in my query. It's pretty straight forward in my opinion. That's why I don't understand.

I uploaded my criteria as well as the results that are returned. You can see the "N" is still included in the results set.

Pat Hartman · Dec 12, 2017

You may have a corrupted index. Try compact & repair. If that doesn't work, export the table to a csv. Make a copy of the table schema and then delete the table. Compact and repair. Rename the empty table and import the .csv into it.

DataAnalyst86 · Dec 12, 2017

Pat Hartman said:
You may have a corrupted index. Try compact & repair. If that doesn't work, export the table to a csv. Make a copy of the table schema and then delete the table. Compact and repair. Rename the empty table and import the .csv into it.

It's a table linked via ODBC. Not sure if that makes a difference or not. Also there's approx. 6 million records in it. So exporting to csv doesn't seem too feasible as the cut off is 65,000 for excel. Thanks.

Mark_ · Dec 12, 2017

DataAnalyst86 said:
It's a table linked via ODBC. Not sure if that makes a difference or not. Also there's approx. 6 million records in it. So exporting to csv doesn't seem too feasible as the cut off is 65,000 for excel. Thanks.

Is this error in EXCEL or ACCESS?

If this is with ACCESS, why would you need to worry about EXCEL?

DataAnalyst86 · Dec 12, 2017

Mark_ said:
Is this error in EXCEL or ACCESS?

If this is with ACCESS, why would you need to worry about EXCEL?

It was suggested that I export the table to a CSV file. The limit is 65,000 records. I have nearly 6 million records in the table.

Mark_ · Dec 12, 2017

DataAnalyst86 said:
It was suggested that I export the table to a CSV file. The limit is 65,000 records. I have nearly 6 million records in the table.

CSV files themselves have not hard limit on records. CSV is 'Comma separated value'.

jdraw · Dec 12, 2017

For clarity, you have an Access table that is linked to ???.

Have you tried working with a smaller sample to get the logic correct and tested? then move to the next issue.

6 million records seems to be a large number of records to be using to track down an issue - have you considered reviewing the data to see what values actually exist in the field Preferred

You could even try where left(preferred,1) ="Y" ---just in case there is an unprintable in the field.

DataAnalyst86 · Dec 12, 2017

jdraw said:
For clarity, you have an Access table that is linked to ???.

Have you tried working with a smaller sample to get the logic correct and tested? then move to the next issue.

6 million records seems to be a large number of records to be using to track down an issue - have you considered reviewing the data to see what values actually exist in the field Preferred

You could even try where left(preferred,1) ="Y" ---just in case there is an unprintable in the field.

I exported records from the table and imported them into a new table so I would have a smaller sample size. My query logic works when I do that. I guess the issue lies within this large table in which I am linking to.

Pat Hartman · Dec 12, 2017

Since you never mentioned the ODBC table, I assumed it was ACE or Jet hence my directions to export to .csv so you could fix the index.

We can only help you when we know your environment.

Talk to your DBA and ask him to rebuild the table and indexes for you unless you know how to do it yourself.

Records not matching with Criteria (1 Viewer)

DataAnalyst86

Registered User.

plog

Banishment Pending

Ranman256

Well-known member

DataAnalyst86

Registered User.

DataAnalyst86

Registered User.

plog

Banishment Pending

DataAnalyst86

Registered User.

Attachments

Pat Hartman

Super Moderator

DataAnalyst86

Registered User.

Mark_

Longboard on the internet

DataAnalyst86

Registered User.

Mark_

Longboard on the internet

jdraw

Super Moderator

DataAnalyst86

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread