Filter problem (incorrect results) in Access 2007

[Milliard]

I am doing a LIKE query (Like "####.####") on a field and about 40% of my 13,000+ records are listed. A corresponding NOT LIKE query appears to list all the other records. The point is that I cannot find any difference between enrtries in the results of the two queries. I was using the query to look for any entries in that field that did not conform to the mask and there is only the odd one in the database. Does Access 2007 choke on large numbers of records?

[TheSmileyCoder]

Hi Millard and welcome to Bytes.

No Access, does not choke on large number of records (atleast not 13.000). Of course this will depend on the amount of columns. I think the largest table I have used so far ran about 500.000 entries with 10 or so columns consisting of both date, numbers and text (no memo or object).

You will have to go over your data once more. There is nothing more I can offer without more information from you.

[Rabbit]

So your data will always have 4 digits followed by a period followed by 4 more digits?

[Milliard]

I've just solved the problem - I switched off Unicode Compression. Doing a 'Find and Replace' on the full stop/decimal point got the same data split as the filter so I wondered about raw data storage vs presentation in the table and cast a suspicious eye at Unicode compression.

I wanted to post this finding against http://bytes.com/topic/access/answer...sion-good-evil
for others to refer to but maybe it's too old.

[Milliard]

Access is a piece of #$%^. After I solved the problem Access 'unsolved' it for me and turning off the Unicode compression no longer works. This is typical of how things go for me - methods stop working. I am an SEA - Software Entropy Attractor - and it's doing my head in.

[Rabbit]

You could use VBA to set or unset the option as soon as the database opens.

[Milliard]

After finding a more in-depth article from M$ on input masks it turned out to be the second parameter - "0" to store all the characters with the data, "1" to not. But I should have had to apply a format to get the data to display with a "." and that wasn't the case. Somehow the input mask is affecting field display (as opposed to input) as well and this shouldn't be so.