query picking up blanks to nulls as relevant

Posted on 2012-09-07
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

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now