Query "or" Criteria (1 Viewer)

Gismo

Registered User.
Local time
Today, 21:20
Joined
Jun 12, 2017
Messages
1,298
Hi all,

Please could you advise how to continate the following

If Type = Airframe, then no Serial number selected, I only want to see the Airframe records
If Type = Engines, then Serial number is selected, I only want to see the Engine records with the selected serial number

1657096355135.png


SELECT [Aircraft Overview Temp].Active, [Aircraft Overview Temp].[Aircraft Registration], [Aircraft Overview Temp].Type, [Aircraft Overview Temp].[Engine Serial #], [Aircraft Overview Temp].Chapter, [Aircraft Overview Temp].[Task number], [Aircraft Overview Temp].[Part Number], [Aircraft Overview Temp].[Serial Number1], fncConcat([Description 2],[Description 1]) AS Description1, [Aircraft Overview Temp]![Limit 1] & " " & [Aircraft Overview Temp]![Unit 1] AS Limit1, [Aircraft Overview Temp]![Limit 2] & " " & [Aircraft Overview Temp]![Unit 2] AS Limit2, [Aircraft Overview Temp]![Limit 3] & " " & [Aircraft Overview Temp]![Unit 3] AS Limit3, [Aircraft Overview Temp].[Consumed 1], [Aircraft Overview Temp].[Consumed 2], [Aircraft Overview Temp].[Consumed 3], [Aircraft Overview Temp].[Installation FH1], [Aircraft Overview Temp].[Installation FH2], [Aircraft Overview Temp].[Installation FH3], [Aircraft Overview Temp].Interval, [Aircraft Overview Temp].[Installation Date 1], [Aircraft Overview Temp].[Limit 1], [Aircraft Overview Temp].[Limit 2], [Aircraft Overview Temp].[Limit 3], Replace([Aircraft Overview Temp]![Part Number],Chr(10)," / ") AS [Split Part Nmber]
FROM [Aircraft Overview Temp]
WHERE ((([Aircraft Overview Temp].Type)="Airframe")) OR ((([Aircraft Overview Temp].Type)="Engines") AND (([Aircraft Overview Temp].[Engine Serial #])=[Forms]![AircraftOverview_Edit]![EngineSerial]))
ORDER BY [Aircraft Overview Temp].[Engine Serial #], [Aircraft Overview Temp].Chapter, [Aircraft Overview Temp].[Task number], [Aircraft Overview Temp].[Part Number];
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Feb 19, 2013
Messages
16,553
on the basis your table contains a serial number for every engine and the [Forms]![AircraftOverview_Edit]![EngineSerial] is not populated, in the serialno criteria on the airframe line put
[Forms]![AircraftOverview_Edit]![EngineSerial] isnull

otherwise (not tested) you should have on your a form a type control and modify the criteria to use that - so instead of 'Airframe' or 'Engines' you would have

=[Forms]![AircraftOverview_Edit]![EngineSerial].[cboType]

Pretty sure you will have been told this many times but note that:
-Type is a reserved word and should not be used for field names
-Using spaces in field names is a bad idea - it will come back to bite you at some point
-Using non alphanumeric characters should also be avoided in field names , particularly # which is used as a date delimiter in access sql - can generate misleading errors
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:20
Joined
Feb 19, 2002
Messages
42,972
What is wrong with what you posted? Granted Access goes crazy with parentheses but if you normalize them, you end up with:

WHERE ([Aircraft Overview Temp].Type ="Airframe" ) OR ([Aircraft Overview Temp].Type ="Engines" AND [Aircraft Overview Temp].[Engine Serial #] =[Forms]![AircraftOverview_Edit]![EngineSerial])

Take CJ's naming suggestions to heart.
 

Users who are viewing this thread

Top Bottom