How to get Not Like statement to work in Access

Question

How to get Not Like statement to work in Access

Starr N 20

I'm trying to isolate typing mistakes in one column of data vs. another using the "not like" statement in an Access query but it doesn't seem to be working. For example, I have two columns of numbers and a third "total" column. If the numbers in the total column are "not like" the first two columns added together, I want it to show the rows that dont add up but it keeps giving me the opposite. Can someone help?

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
George Hepworth 22,220 Reputation points Volunteer Moderator

2025-10-01T19:09:38.5366667+00:00

It's generally helpful to include sample data when asking questions like this.

But a few things come to mind.

You refer to this as "not like", but in addition problems, the usual phrase would be "not equal", mathematically. I assume that's what you intend, correct?

It's a fairly simple math equation, if that's indeed what you need.

But before we get that far, this sounds very much like one of the problems we warn people about when they try to save a calculated value in a field in a table. They frequently go out of sync, leaving you with unreliable data in the calculated field. Unreliable data is worse than no data, because it is misleading.

In this case, without knowing the work flow you are tracking, it's not possible to be totally specific, but I would usually advice people in this predicament to delete the calculated field and use the basic math calculation anywhere in the database you need it at the time.

Tell us more about what is really being stored, and why, and we'll be in a better position to offer usable advice.

In fact, the comparison is very simple, but I'm actually reluctant to give it because that might tempt you not to correct the improper table design.

Answer accepted by question author

0 additional answers

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
George Hepworth 22,220 Reputation points Volunteer Moderator

2025-10-01T19:09:38.5366667+00:00

It's generally helpful to include sample data when asking questions like this.

But a few things come to mind.

You refer to this as "not like", but in addition problems, the usual phrase would be "not equal", mathematically. I assume that's what you intend, correct?

It's a fairly simple math equation, if that's indeed what you need.

But before we get that far, this sounds very much like one of the problems we warn people about when they try to save a calculated value in a field in a table. They frequently go out of sync, leaving you with unreliable data in the calculated field. Unreliable data is worse than no data, because it is misleading.

In this case, without knowing the work flow you are tracking, it's not possible to be totally specific, but I would usually advice people in this predicament to delete the calculated field and use the basic math calculation anywhere in the database you need it at the time.

Tell us more about what is really being stored, and why, and we'll be in a better position to offer usable advice.

In fact, the comparison is very simple, but I'm actually reluctant to give it because that might tempt you not to correct the improper table design.

Answer 1

Tammy-Ng 5,585 Microsoft External Staff Moderator

Dear Starr N
Welcome, and thank you for sharing your issue in the Q&A forum.

Based on the information you provided, I understand that you're trying to isolate rows where the "Total" column does not match the sum of two other numeric columns, using the NOT LIKE statement. However, you're getting the opposite result from what you expected.

The core issue here is the misuse of the NOT LIKE operator. This operator is designed for pattern matching in text fields, not for performing numeric comparisons. When applied to numbers, it can produce inaccurate or unexpected results.

To compare numeric values properly, you should use the "not equal to" operator (<>) instead of NOT LIKE. Here's the correct syntax:

[Total Column] <> ([Column1] + [Column2])

Show more lines

By replacing NOT LIKE with <> in your query criteria, Access will correctly display only the rows where the sum is incorrect.
To ensure this solution works perfectly for your data environment, I’d appreciate it if you could provide a bit more detail:

Data Types: Could you confirm whether the columns you're working with (Column1, Column2, and Total) are formatted as Number or Text in Access?
Sample Data: If possible, please share a few sample rows (5–10) from these columns. This will help me test the logic and ensure the query syntax works correctly with your actual data, including any edge cases.

Your input will be very helpful in finding the best solution for your situation. Thanks again, and I look forward to your reply!

George Hepworth 22,220 Reputation points Volunteer Moderator

2025-10-02T16:55:20.4166667+00:00

Unfortunately, this is the solution I refrained from providing because it preserves the inappropriate table design which leads to the problem in the first place.

It is not desirable to store calculated values precisely because it leads to inconsistencies that you then have to invest time and effort tracking down later.
Starr N 20 Reputation points

2025-10-02T17:17:38.0866667+00:00

Thank you Tammy for your help - I very much appreciate it. It did what I needed it to do. Here is a snippet of the table. The columns are in numeric format. Thank you George for your input as well.
Ken Sheridan 3,546 Reputation points

2025-10-04T15:04:39.3833333+00:00

On the basis of the data shown in your query's datasheet I would be very surprised if the solution which Tammy-Ng gave you would return the correct results. If we take the following row as an example:

0050050 7 3 Null 10

The third column here is Null (empty). In arithmetic expressions Null propagates i.e. any expression which includes a Null will evaluate to Null. This will be clear if you understand that, as the absence of a value, Null can be understood as 'unknown', and not as zero, so:

7 + 3 + 'unknown'

must evaluate, not to 10, but to 'unknown', i.e. to Null.

Similarly Null can never be equal to or unequal to anything. Ther result will always be Null, neither TRUE nor FALSE.

Because, when processing data, we will at times have to allow for Nulls, Access provides a Nz function, which will return zero by default, or whatever you specify as the optional second argument when calling the function, in place of Null. Otherwise it will return the value passed in as the first argument. When we want to return the result of an expression which includes Null, but interpreting Null as zero, the expression will be along these lines:

Nz([BS US Men],0) + Nz([Bs US Wmn],0) + Nz([BS US AI],0) <> Nz([BS US Tot],0)

This will evaluate to TRUE or FALSE whether or not any of the column positions are Null, including rows where the first three column positions are Null and the [BS US Tot] is either Null or zero.

However, none of the above should be necessary in a correctly designed relational database because [BS US Tot] should not be a column in the base table, but only in the query's result table. The query should, therefore, include the following computed column, entered in query design view as follows in the 'field' row of an empty column:

[BS US Tot]:Nz([BS US Men],0) + Nz([Bs US Wmn],0) + Nz([BS US AI],0)

It is now guaranteed that the total will always be correct, and the possibility of update anomalies arising from the storage of the total in a column in the base table is avoided.

The above assumes that Null is allowed at the column positions of course. There is, however, a good argument for columns of numerical or currency data having a DefaultValue of 0, and a Required property of True (Yes). If you are really purist, the formal database relational model disallows Nulls in tables completely, on the basis that each attribute a at a column position in a row in a table must be a legitimate value of type a. We've seen above that Null is not a value, but the absence of a value, so the formal model does not allow Nulls. Chris Date, one of the founders of the database relational model argues strongly for this in the literature. However, Ted Codd, who first introduced the model in 1970, proposes a 3-way logic with values TRUE, FALSE and UNKNOWN, rather that the Boolean values TRUE and FALSE only, so implicitly supports Nulls. We mere mortals must make up our own minds therefore.

If you decide to follow Date's advice then you can easily replace all the Nulls in a table with zeros by means of a simple update query, calling the Nz function. Note, however, that columns of DateTime data type present a problem as regards a default value because, DateTime values are just 64 bit floating point numbers under the skin, and a default value of zero would represent 30 December 1899 00:00:00, which is probably not what we'd want as a default. If you are wondering why such a weird value was chosen as date/time zero, it has been said that it was because the original designers of Access got the algorithm for determining which years are leap years wrong, but I'm not aware of any evidence for that theory.

Share via

How to get Not Like statement to work in Access

0 additional answers

Your answer