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

[hatsufi]

I am trying to create a report based on a query. I have 2 different yes/no checkbox fields and amy trying to filter my report results based on both of those check box fields being "checked" Please help, i cannot find a way to make that work!
My records either have field1 check box checked, field2 check box checked, or NO checks. How do I filter my report to use only the records that have a check in one or the other?

 

[hatsufi]

I do have a report created that filters one of the check boxes and filters it upon opening the report. How do a add the second check box in to that?

 

[CJ_London]

would help if you provided the sql you are having a problem with but I would expect the criteria to be

WHERE field1=true OR field2=true

 

[hatsufi]

would help if you provided the sql you are having a problem with but I would expect the criteria to be

WHERE field1=true OR field2=true

So I am not good at writing code LOL, but in my report properties, in the "filter", i have the following ([Montezuma Congregation 2022 Individual Record].[Widow] Not In (0)) and that filters the "widows" out, but I also have a "widowers" field and I want my report to include both widows and widowers. They are checkboxes for each record whether they are a widow or widower.

 

[hatsufi]

So I am not good at writing code LOL, but in my report properties, in the "filter", i have the following ([Montezuma Congregation 2022 Individual Record].[Widow] Not In (0)) and that filters the "widows" out, but I also have a "widowers" field and I want my report to include both widows and widowers. They are checkboxes for each record whether they are a widow or widower.

The query I am basing my report on is used to convert birthdate to age off of a table. I want to include the age in my report, so i am basing the report on the query insted of the table. Following is the SQL of my query.

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

 

[CJ_London]

copy this sql with the WHERE clause included into the sql window of a new query - names as you have provided

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

ORDER BY [Montezuma Congregation 2022 Individual Record].[Household Last Name], [Montezuma Congregation 2022 Individual Record].[Household First Name], [Montezuma Congregation 2022 Individual Record].[Household Rank];

swap to the query window and you will see 'True' on one line of the criteria section under the widows field and again on the next line for the widowers column. In both cases the show box should be unticked. Tick them if you want their widow/er status to be available for your report

 

[hatsufi]

copy this sql with the WHERE clause included into the sql window of a new query - names as you have provided

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

ORDER BY [Montezuma Congregation 2022 Individual Record].[Household Last Name], [Montezuma Congregation 2022 Individual Record].[Household First Name], [Montezuma Congregation 2022 Individual Record].[Household Rank];

swap to the query window and you will see 'True' on one line of the criteria section under the widows field and again on the next line for the widowers column. In both cases the show box should be unticked. Tick them if you want their widow/er status to be available for your report

Thank you for your help, i pasted that in to a new query SQL, not sure what you mean by WHERE CLAUSE, but in design view, only widow had TRUE. I wanted both Widower and Widow to have TRUE, but when I put TRUE in the WIDOWER, then run the query, it is all blank.. Sorry I am do dumb!!!! Any more help?

 

[hatsufi]

copy this sql with the WHERE clause included into the sql window of a new query - names as you have provided

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

ORDER BY [Montezuma Congregation 2022 Individual Record].[Household Last Name], [Montezuma Congregation 2022 Individual Record].[Household First Name], [Montezuma Congregation 2022 Individual Record].[Household Rank];

swap to the query window and you will see 'True' on one line of the criteria section under the widows field and again on the next line for the widowers column. In both cases the show box should be unticked. Tick them if you want their widow/er status to be available for your report

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


That is what I have now. I see the WHERE clause, but it did not fix it?

 

[SHANEMAC51]

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

 

[hatsufi]

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

Thank you very very much. Let me try that in the morning!

 

[CJ_London]

shanemac has provided the same code I have except he has aliased the table to a short name to make it more readable

I provided

WHERE [Montezuma Congregation 2022 Individual Record].[Widow] = true OR [Montezuma Congregation 2022 Individual Record].[widowers]=True

you have

WHERE ((([Montezuma Congregation 2022 Individual Record].Widow)=True) AND (([Montezuma Congregation 2022 Individual Record].Widower)=True)) OR ((([Montezuma Congregation 2022 Individual Record].[widowers])=True))

which implies in the query window you have two trues on one line - it should look more like this

ignore the Expr1 - I didn't bother trying to make a table to match yours

 

[CJ_London]

you could simplify and have just one field 'widowed' - you can then determine whether they are a widow or widower based on their gender

 

[hatsufi]

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

Thank you SO MUCH! I really appreciate the help from both of you. The above worked SUPER!! So glad for those of you who are willing to help those of us who don't have all the expertise!! Thanks again!

 

[hatsufi]

you could simplify and have just one field 'widowed' - you can then determine whether they are a widow or widower based on their gender

Thank you SO MUCH! I really appreciate the help from both of you. The above worked SUPER!! So glad for those of you who are willing to help those of us who don't have all the expertise!! Thanks again!

 

[CJ_London]

assuming you want 'widow OR widower OR single', just include a True criteria on the next line down in the criteria section for the Single field

 

[GPGeorge]

As is so often the case, an inappropriate table design leads to superficially easier appearing data entry via two checkboxes on a form, but behind the scenes, it leads to extra hurdles to get the data back out in a usable form.

As a couple of people have suggested, two yes/no fields here are, in fact, coding DATA into the name of a field. You have a single Status here -- more precisely "Marital Status". There are at least three possible values for "Marital Status": "Single", "Married", "Widowed" and any others you might want to include for the demographics of your situation. I'm going to go further and say that a separate field called "Gender" is important, as someone also suggested. The gender of the person comes into play when referring to someone as "Widow" or as "Widower", but not necessarily when referring to someone as "Single" or "Married". However, in ALL cases, I would assume, you need to record gender for other purposes.

So, instead of multiple yes/no fields, you have a single field, "MaritalStatus", which can have one of a handful of values. as required by your organization's purposes. The beauty of this approach is not just in simplifying queries, it is in the ability to add a new record to a lookup table of Marital Statuses as needed--which requires NO modification to interface or queries or reports.

You have choices, of course. Access is highly tolerant. It allows us to implement non-standard table designs and code up magnificent workarounds to handle them. But it also designed to follow sound relational database principles, and that's the highest level of professionalism.

 

[hatsufi]

Thank you all for your help. How would i set up a "Marital Status" with the choices? Sorry, I am ignorant!!! Also, I got the report designed, but I am having a glitch I cannot figure out. See attachment. How do i keep the top of the columns lined up, OR how can I force the group to the NEXT column?

 

[hatsufi]

Thank you all for your help. How would i set up a "Marital Status" with the choices? Sorry, I am ignorant!!! Also, I got the report designed, but I am having a glitch I cannot figure out. See attachment. How do i keep the top of the columns lined up, OR how can I force the group to the NEXT column?

Acutally just got it figured out how to do the choices, but still can NOT get my report straightened out!

 

[GPGeorge]

Acutally just got it figured out how to do the choices, but still can NOT get my report straightened out!

Sorry, my report skills are open to question. I think you need to adjust the way groups are handled to get the layout desired.
Two properties of relevance could be "Keep Together" and "New Row or Column".

 

[hatsufi]

Sorry, my report skills are open to question. I think you need to adjust the way groups are handled to get the layout desired.
Two properties of relevance could be "Keep Together" and "New Row or Column".
View attachment 101838

I have played with that all ways, and can not find a way to align tops, yet keep all on the first page