query picking up blanks to nulls as relevant

Posted on 2012-09-07
Medium Priority
Last Modified: 2012-09-09
I am in access and I use the following to determine if data has changed from month to month however i believe its picking up blanks to null or vice versa:

SELECT AbiCurrentCodes.abiCode, (Trim(Nz([AbiCurrentCodes].Model_Description))<>Trim(Nz(AbiCodesPrevious.Model_Description))) AS ModelDescdiff, (Trim(Nz([AbiCurrentCodes].Manufactured_From))<>Trim(Nz(AbiCodesPrevious.Manufactured_From))) AS ManfFromdiff, (Trim(Nz([AbiCurrentCodes].Manufactured_To))<>Trim(Nz(AbiCodesPrevious.Manufactured_To))) AS ManfTodiff, (Trim(Nz([AbiCurrentCodes].Series))<>Trim(Nz(AbiCodesPrevious.Series))) AS Seriesdiff, (Trim(Nz([AbiCurrentCodes].Engine_CC))<>Trim(Nz(AbiCodesPrevious.Engine_CC))) AS EngineCCdiff, (Trim(Nz([AbiCurrentCodes].abiDOORS))<>Trim(Nz(AbiCodesPrevious.abiDOORS))) AS AbiDoorsdiff, (Trim(Nz([AbiCurrentCodes].Body_type))<>Trim(Nz(AbiCodesPrevious.Body_type))) AS AbiBodydiff, (Trim(Nz([AbiCurrentCodes].Engine_Type))<>Trim(Nz(AbiCodesPrevious.Engine_Type))) AS EngineTypediff, (Trim(Nz([AbiCurrentCodes].[Transmission_type]))<>Trim(Nz([AbiCodesPrevious].[Transmission_type]))) AS AbiTransmissiondiff, (Trim(Nz([AbiCurrentCodes].Gross_kg))<>Trim(Nz(AbiCodesPrevious.Gross_kg))) AS Grosskgdiff
FROM AbiCurrentCodes INNER JOIN AbiCodesPrevious ON AbiCurrentCodes.abiCode = AbiCodesPrevious.abiCode
WHERE ((((Trim(Nz([AbiCurrentCodes].[Model_Description]))<>Trim(Nz([AbiCodesPrevious].[Model_Description]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Manufactured_From]))<>Trim(Nz([AbiCodesPrevious].[Manufactured_From]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Manufactured_To]))<>Trim(Nz([AbiCodesPrevious].[Manufactured_To]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Series]))<>Trim(Nz([AbiCodesPrevious].[Series]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Engine_CC]))<>Trim(Nz([AbiCodesPrevious].[Engine_CC]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[abiDOORS]))<>Trim(Nz([AbiCodesPrevious].[abiDOORS]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Body_type]))<>Trim(Nz([AbiCodesPrevious].[Body_type]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Engine_Type]))<>Trim(Nz([AbiCodesPrevious].[Engine_Type]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Transmission_type]))<>Trim(Nz([AbiCodesPrevious].[Transmission_type]))))<>False)) OR ((((Trim(Nz([AbiCurrentCodes].[Gross_kg]))<>Trim(Nz([AbiCodesPrevious].[Gross_kg]))))<>False));

Open in new window

Question by:PeterBaileyUk
1 Comment
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 38375650
Are you trying to omit blanks and nulls?

Try adding this to the endof your SQL statement, immediatelt before the semicolon:

AND "" & [Gross_kg] <> "" AND  "" & TransmissionType <> ""

Start with a manageable number of fields... and if the results look for those specific fields, extend the same concept to include all relevant fields.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question