Report filtered by 2 yes/no fields (1 Viewer)

[The_Doc_Man]

I'm going to simplify your life a little more...

SELECT [Household Last Name], [Household First Name], [First Name], [Last Name], [Date of Birth], [Home Phone], [Fax Number], [Cell Phone], Address, City, State, [Zip Code], Email, Youth, Widow, Widower, [Church Member], DateDiff("yyyy",[Date of Birth],Date())+(Format([Date of Birth],"mmdd")>Format(Date(),"mmdd")) AS Expr1, [Household Rank]
FROM [Montezuma Congregation 2022 Individual Record]
WHERE (((Widow)=True) AND ((Widower)=True)) OR ((([widowers])=True))
ORDER BY [Household Last Name], [Household First Name], [Household Rank];

Since you have only one data source in your FROM clause, you can omit the prefix entirely. There is no other place to look unless you have a constant or some function (e.g. Date and Format) - and functions don't need a prefix for their arguments either, because in all such cases, the current data source (FROM clause) is assumed. You only need prefixes when you have a JOIN clause with two tables and some of the field names happen to overlap, or when you have a same-table sub-query where field names are certain to overlap. In either of those latter two cases, you have to use aliases to specify which JOINed table's record or which instance (main or sub) of the table has the right data. And you would use more complex prefixes when referencing a control on a form. Otherwise, just use the field name, with brackets when required (see last comment.)

I'm not going to guess at what is or what isn't a field in your table here, but I'm fairly sure that if the fields have conventional meanings to match their names, then in your WHERE clause, (Widow=TRUE) AND (Widower=TRUE) cannot ever happen. Could be an OR case, but not an AND case. I didn't see a field Widowers or an alias for it in the SELECT clause, but SQL allows you to name a field in a WHERE clause that is not part of the SELECT clause, so I guess it could happen.

I see also something that catches my eye and leads to the question, "Do you have a table [Montezuma Congregation 2021 Individual Record]?" (Or will you have a table for 2023?) If so, the year should NOT be the basis for making a new table. What SHOULD happen (based on considerations of normalization) is that you would have one table with a person's information with (at most) a start and end year for their membership. (AND even that is denormalized!) IF you have the case of one table per year, you WILL run into the issue of having to duplicate the forms and reports for each year or will have to work other types of trickery and legerdemain to refer to other years' records. That is, you will have the issue if you haven't already run into it.

Then, one more general comment that right now won't make a huge difference, but in the long run, you probably should reduce the number of field names with blanks in them, eventually to 0 cases thereof. Use abbreviations, or just run the words together. THEN you can get rid of a LOT of square brackets. Consider shorter names for every field, to use abbreviations where possible, to reduce the number of characters to be typed. I can touch-type pretty well but that doesn't mean I like repetitively typing long names. This suggestion is NOT a technical suggestion, but rather is one of convenience and practicality for later down the road.