Hi Connie!

So, you're trying to get something like this?

If Department ≠ Ag Service Board AND Crew ≠ Maintenance Graders then SELECT ALL WHERE Plans ≠ "Grassfire Awareness"

If Department = Ag Service Board then SELECT ALL WHERE Department = Ag Service Board

If Crew = Maintenance Graders then SELECT ALL WHERE Crew = Maintenance Graders

Have I correctly understood the goal?

If I have correctly determined it, then 2nd and 3rd one are fairly easy since they are just evaluating equality, which Forms does easily with lookups.  But the 1st one is not so easy since it's evaluating inequality, which Forms doesn't really do.  You could probably make it work in Forms with check values for the Department and Crew fields, and ensuring they are blank if they are not those two expected values.

Thanks, Matthew!!

So, it's:  Select Form Number "ALL", but if Department is "Ag Service Board" OR Crew is "Maintenance Graders", add "Grassfire Awareness".

Your first option would work, I think, for all the normal ones, but how do we get it to work for the exceptions?

But, you've given me an idea...  no, wait... still doesn't fit all possibilities.

Ultimately, I need three options, but how to get them... I thought I could have hidden fields collecting the department and crew fields, then have the if statement of If (this) or (that) then _____ and there would be another column in the SQL table to match up with.  But I haven't been able to get the if statement working and haven't been able to decide on what the extra column in the SQL would be yet.  Do you think something like this would work?

I've unhidden my collector fields for now and you can see the Part 2 isn't collecting anything yet (simply because I haven't found how to say "if Part 1 contains ______ instead of equals _____ [one of the two exceptions]).

For the way you are describing it, I think you're going to be best served by a Stored Procedure, which works a little differently than a View.
Where a View is basically just a saved query, a Stored Procedure has a lot more ability to manipulate input data to return different results.

If you want to pursue this, then in SQL Management Studio, you'd go to the "Programmability" folder instead of Tables or Views, then inside there, open the "Stored Procedures" folder, and right-click and select the option to add a new stored procedure.  You'll get something that looks like this:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Notice that says to press Ctrl+Shift+M to populate Template Parameters.  You'll get a popup like this, which you can fill out similar to how I've done in the screenshot (obviously using your name instead of mine and a more appropriate description).

Now it should be looking something like this. 

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Matthew Tingey
-- Create date: April 6, 2022
-- Description:	Demonostration for Connie.
-- =============================================
CREATE PROCEDURE DemoForConnie 
	-- Add the parameters for the stored procedure here
	@Department varchar(50) = , 
	@Crew varchar(50) = 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @Department, @Crew
END
GO

Notice that it is likely showing an error on lines 23 and 24 because those parameters are showing equal to nothing (because I left them blank in that pop-up window).  You can just delete the equal signs here - like this: 

@Department varchar(50), 
@Crew varchar(50)

You may notice, this isn't doing much right now.  It takes in two parameters, the @Department and @Crew, and then returns those same parameters back in the SELECT statement.

From here's we're going to edit that SELECT statement to handle the data you need.

So delete the SELECT statement on line 32, and replace it with the following (tweaking it for the apporiate table name, as I didn't see that in your post before): 

SELECT
  [Form Number],
  [Category],
  [Plans],
  [Department],
  [Crew],
  [Line #]
FROM [TableNameGoesHere]
WHERE
  [Form Number] = 'ALL'
  OR [Department] = @Department
  OR [Crew] = @Crew

This allows the two values to be passed in and will return the rows they match, plus all rows where the [Form Number] is set to ALL.

When you executive this script, it isn't going to run the query, it's going to create the Stored Procedure.  In the folder selector, you can hit Refresh, and you should now see the new Stored Procedure.  You can right-click on it, and select the option to Execute it, which will allow you to test it, passing different values into the @Department and @Crew Parameters.

Once you've fully tested it and you are happy with it, you can go into Forms and refresh your data source so that it can see the new Stored Procedure.

On your form itself, on the lookups, you'll need to change the second field from "Table / View" to "Stored Procedure", but then your new procedure should be an option to select.  You'll be able to pick the two fields to pass in for the @Department and @Crew parameters.  And then the fields that will be populated by the returning values.  Unless it has changed with newer versions, there is a difference with Stored Procedures versus Tables or Views where Forms won't pre-populate the returning column names and you'll need to do it manually (Form Number, Category, Plans, Department, Crew, Line #).

Thanks, Matthew!!  I don't seem to have that option.  When looking, I saw a few messages like "needs to be loaded".  I'm wondering if we purchased a basic pkg and don't have this.  I think I'm going to have to contact my IT to see if this is available.