[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

previously null

I have an access query that is looking at two tables one current data and the other last months data and the query is picking up where there is a difference.

It is not picking up where it was null previously null with the current value populated ie data came in for that particular field.

How can I pick up that scenario too?

not sure if i also want the reverse ie i no longer have data but had it last month ie the current field became null.

am working in access query grid
regards in advance

SELECT SMMT.[MVRIS CODE], IIf(Trim([SMMT].[INTRO DATE])<>Trim([SMMT_Previous].[INTRO DATE]),1,0) AS Introdiff, IIf(Trim([SMMT].[marque])<>Trim([SMMT_Previous].[marque]),1,0) AS MarqueDiff, IIf(Trim([SMMT].[model range])<>Trim([SMMT_Previous].[model range]),1,0) AS ModelRangeDiff, IIf(Trim([SMMT].[Range series])<>Trim([SMMT_Previous].[Range series]),1,0) AS RangeSeriesDiff, IIf(Trim([SMMT].[variant])<>Trim([SMMT_Previous].[variant]),1,0) AS VariantDiffA, IIf(Trim([SMMT].[cc])<>Trim([SMMT_Previous].[cc]),1,0) AS CCDiff, IIf(Trim([SMMT].[nom cc])<>Trim([SMMT_Previous].[nom cc]),1,0) AS NomCCDiff, IIf(Trim([SMMT].[doors])<>Trim([SMMT_Previous].[doors]),1,0) AS DoorsDiff, IIf(Trim([SMMT].[Body Type])<>Trim([SMMT_Previous].[Body type]),1,0) AS BodyTypeDiff, IIf(Trim([SMMT].[Transmission])<>Trim([SMMT_Previous].[Transmission]),1,0) AS TransmissionDiff, IIf(Trim([SMMT].[fuel])<>Trim([SMMT_Previous].[fuel]),1,0) AS FuelDiff, IIf(Trim([SMMT].[aspiration])<>Trim([SMMT_Previous].[aspiration]),1,0) AS AspirationDiff, IIf(Trim([SMMT].[drive type])<>Trim([SMMT_Previous].[drive type]),1,0) AS DriveTypeDiff, IIf(Trim([SMMT].[driving axle])<>Trim([SMMT_Previous].[driving axle]),1,0) AS DrivingAxleDiff, IIf(Trim([SMMT].[forward gears])<>Trim([SMMT_Previous].[forward gears]),1,0) AS ForwardGearsDiff, IIf(Trim([SMMT].[seats])<>Trim([SMMT_Previous].[seats]),1,0) AS SeatsDiff, IIf(Trim([SMMT].[engine model])<>Trim([SMMT_Previous].[engine model]),1,0) AS EngineModelDiff, IIf(Trim([SMMT].[fuel delivery])<>Trim([SMMT_Previous].[fuel delivery]),1,0) AS FuelDelDiff, IIf(Trim([SMMT].[no cylinders])<>Trim([SMMT_Previous].[no cylinders]),1,0) AS NoCylDiff, IIf(Trim([SMMT].[valves per cylinder])<>Trim([SMMT_Previous].[valves per cylinder]),1,0) AS ValvespercylDiff, IIf(Trim([SMMT].[valve gear])<>Trim([SMMT_Previous].[valve gear]),1,0) AS valvegearDiff, IIf(Trim([SMMT].[power kw])<>Trim([SMMT_Previous].[power kw]),1,0) AS powerkwDiff, IIf(Trim([SMMT].[calc bhp])<>Trim([SMMT_Previous].[calc bhp]),1,0) AS calcbhpDiff, IIf(Trim([SMMT].[co2])<>Trim([SMMT_Previous].[co2]),1,0) AS co2Diff, IIf(Trim([SMMT].[gvw])<>Trim([SMMT_Previous].[gvw]),1,0) AS gvwDiff, IIf(Trim([SMMT].[cab type])<>Trim([SMMT_Previous].[cab type]),1,0) AS cabDiff, IIf(Trim([SMMT].[van roof config])<>Trim([SMMT_Previous].[van roof config]),1,0) AS vanroofDiff, IIf(Trim([SMMT].[wheelbase type])<>Trim([SMMT_Previous].[wheelbase type]),1,0) AS wheelbasetypeDiff
FROM SMMT INNER JOIN SMMT_Previous ON SMMT.[MVRIS CODE] = SMMT_Previous.[MVRIS CODE]
WHERE (((IIf(Trim([SMMT].[INTRO DATE])<>Trim([SMMT_Previous].[INTRO DATE]),1,0))>0)) OR (((IIf(Trim([SMMT].[marque])<>Trim([SMMT_Previous].[marque]),1,0))>0)) OR (((IIf(Trim([SMMT].[model range])<>Trim([SMMT_Previous].[model range]),1,0))>0)) OR (((IIf(Trim([SMMT].[Range series])<>Trim([SMMT_Previous].[Range series]),1,0))>0)) OR (((IIf(Trim([SMMT].[variant])<>Trim([SMMT_Previous].[variant]),1,0))>0)) OR (((IIf(Trim([SMMT].[cc])<>Trim([SMMT_Previous].[cc]),1,0))>0)) OR (((IIf(Trim([SMMT].[nom cc])<>Trim([SMMT_Previous].[nom cc]),1,0))>0)) OR (((IIf(Trim([SMMT].[doors])<>Trim([SMMT_Previous].[doors]),1,0))>0)) OR (((IIf(Trim([SMMT].[Body Type])<>Trim([SMMT_Previous].[Body type]),1,0))>0)) OR (((IIf(Trim([SMMT].[Transmission])<>Trim([SMMT_Previous].[Transmission]),1,0))>0)) OR (((IIf(Trim([SMMT].[fuel])<>Trim([SMMT_Previous].[fuel]),1,0))>0)) OR (((IIf(Trim([SMMT].[aspiration])<>Trim([SMMT_Previous].[aspiration]),1,0))>0)) OR (((IIf(Trim([SMMT].[drive type])<>Trim([SMMT_Previous].[drive type]),1,0))>0)) OR (((IIf(Trim([SMMT].[driving axle])<>Trim([SMMT_Previous].[driving axle]),1,0))>0)) OR (((IIf(Trim([SMMT].[forward gears])<>Trim([SMMT_Previous].[forward gears]),1,0))>0)) OR (((IIf(Trim([SMMT].[seats])<>Trim([SMMT_Previous].[seats]),1,0))>0)) OR (((IIf(Trim([SMMT].[engine model])<>Trim([SMMT_Previous].[engine model]),1,0))>0)) OR (((IIf(Trim([SMMT].[fuel delivery])<>Trim([SMMT_Previous].[fuel delivery]),1,0))>0)) OR (((IIf(Trim([SMMT].[no cylinders])<>Trim([SMMT_Previous].[no cylinders]),1,0))>0)) OR (((IIf(Trim([SMMT].[valves per cylinder])<>Trim([SMMT_Previous].[valves per cylinder]),1,0))>0)) OR (((IIf(Trim([SMMT].[valve gear])<>Trim([SMMT_Previous].[valve gear]),1,0))>0)) OR (((IIf(Trim([SMMT].[power kw])<>Trim([SMMT_Previous].[power kw]),1,0))>0)) OR (((IIf(Trim([SMMT].[calc bhp])<>Trim([SMMT_Previous].[calc bhp]),1,0))>0)) OR (((IIf(Trim([SMMT].[co2])<>Trim([SMMT_Previous].[co2]),1,0))>0)) OR (((IIf(Trim([SMMT].[gvw])<>Trim([SMMT_Previous].[gvw]),1,0))>0)) OR (((IIf(Trim([SMMT].[cab type])<>Trim([SMMT_Previous].[cab type]),1,0))>0)) OR (((IIf(Trim([SMMT].[van roof config])<>Trim([SMMT_Previous].[van roof config]),1,0))>0)) OR (((IIf(Trim([SMMT].[wheelbase type])<>Trim([SMMT_Previous].[wheelbase type]),1,0))>0))
ORDER BY SMMT.[MVRIS CODE];

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try using the Bz() fucntion

Example:

where IIf( Trim(Nz([SMMT].[INTRO DATE])) <> Trim(Nz([SMMT_Previous].[INTRO DATE])) ,1,0) >0  Or 

Open in new window


or simplified to

where IIf( Trim(Nz([SMMT].[INTRO DATE])) <> Trim(Nz([SMMT_Previous].[INTRO DATE])) ,True,False)  Or 

Open in new window


even more simplified:

where (Trim(Nz([SMMT].[INTRO DATE]) <> Trim(Nz([SMMT_Previous].[INTRO DATE]))) Or 

Open in new window


Why all the TRIM()'s?

Are the dates stored as text not data/time data type?



0
 
PeterBaileyUkAuthor Commented:
text unfortunately
0
 
PeterBaileyUkAuthor Commented:
wanted to get rid of possible left and or right spaces at the end of the strings
0
 
PeterBaileyUkAuthor Commented:
dont know this function Bz() ;)
0
 
PeterBaileyUkAuthor Commented:
went for  (Trim(Nz([SMMT].[INTRO DATE]))<>Trim(Nz([SMMT_Previous].[INTRO DATE])))
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now