Solved FILTER BY FORM

[mercystone]

you have 12 tables, but only 4 of them (Figure 2) are important for the scheme
, two tables (for GENDER and school) are embedded in the table, students
marked the numeric fields in green, which are formatted without quotes in the filter
, orange are text fields, in quotes

substitutions usually return the record code (numeric), except for the built-in ones (they return text)
, so all this should be taken into account, and parentheses will not hurt for reliability

DIM F1,F2,F3,F4,F5,F6
f1=" AND ( year =2020)"
f2=" AND (gender='FEMILE')"


...
FILTER=MID(F1 & F2 & F3,5)

in the general report, a value is returned from substitutions (numeric for the year, the rest are text), the report is configured for these conditions

in the student's report, everything is different, substitutions return the code and the report does not open

conclusion:
1. substitutions should be done the same way, (directory code) + (directory text), do not make built-in substitutions (all external, from directories)
2. it may even be more convenient for you to always return text directly when entering information (all fields in tables are text), you will not need to re-poll directories, although for large tables it is memory overruns

Thanks for going an extra mile. I don't take it lightly. Thanks also for your explanations. I really appreciate.

 

[mercystone]

you have 12 tables, but only 4 of them (Figure 2) are important for the scheme
, two tables (for GENDER and school) are embedded in the table, students
marked the numeric fields in green, which are formatted without quotes in the filter
, orange are text fields, in quotes

substitutions usually return the record code (numeric), except for the built-in ones (they return text)
, so all this should be taken into account, and parentheses will not hurt for reliability

DIM F1,F2,F3,F4,F5,F6
f1=" AND ( year =2020)"
f2=" AND (gender='FEMILE')"


...
FILTER=MID(F1 & F2 & F3,5)

in the general report, a value is returned from substitutions (numeric for the year, the rest are text), the report is configured for these conditions

in the student's report, everything is different, substitutions return the code and the report does not open

conclusion:
1. substitutions should be done the same way, (directory code) + (directory text), do not make built-in substitutions (all external, from directories)
2. it may even be more convenient for you to always return text directly when entering information (all fields in tables are text), you will not need to re-poll directories, although for large tables it is memory overruns

Hello. When I create a report using crosstab query it works perfectly. I am able to filter the reports for CLASSES FORM 1, FORM 2, FORM 3 and FORM 4. It works perfectly with no problems. I think the problem is not with tables and forms but rather MAIN REPORT- REPORT FORMS and SUBREPORT rptStudentsMarkssubreport. I can't figure it out. Somebody please to assist me. Crosstab query works perfectly upto and including report filter.
Kindly confirm it here with the help of @arnelgp , @dbguy and @cjlondon.

Solved - DYNAMIC REPORT FROM CROSSTAB QUERY

Hi. Somebody please help me. I am defeated. Kindly look at the attached database. I am trying to create dynamic report from qrygrades but I am defeated on how to do it such that if a subject is added it is automatically populated to report field list(check report- all learners examination). The...

www.access-programmers.co.uk

 

[mercystone]

I followed this thread and applied in my database which I used above tables and it works perfectly.

 

[SHANEMAC51]

It works perfectly with no problems. I think the problem is not with tables and forms but rather MAIN REPORT- REPORT FORMS and SUBREPORT rptStudentsMarkssubreport.

 

[SHANEMAC51]

Student ID	Last Name	First Name	Gender	NAME OF THE SCHOOL	KCPE	Grade Year ID	Grade ID	Stream ID	Term ID	Exami nation ID	Subject ID	Mark	SubjectAbr	Description
2	CHEP KOECH	MILLI CENT	FEMALE	MARUM BASI SECON DARY SCHOOL	430	1	1	1	1	2	14	50	AGRI	Agriculture
2	-/-	-/-	-/-	-/-	430	4	2	2	2	2	6	60	CHEM	Chemistry
2	-/-	-/-	-/-	-/-	430	1	1	1	1	2	13	90	ART	Art And Design
2	-/-	-/-	-/-	-/-	430	1	1	1	1	2	16	80	AVIA	Aviation
2	-/-	-/-	-/-	-/-	430	1	1	1	1	2	4	60	BIO	Biology
2	-/-	-/-	-/-	-/-	430	1	1	1	2	1	14	80	AGRI	Agriculture
2	-/-	-/-	-/-	-/-	430	1	1	1	2	1	4	90	BIO	Biology
2	-/-	-/-	-/-	-/-	430	1	1	1	2	1	4	100	BIO	Biology

perhaps the table will help you in checking the results

 

[mercystone]

That is why I like this forum. We have experts who are dedicated to help you. Thanks so much @SHANEMAC51 There is a problem with filter SUBJECTS
Let's admit a Student let's call him
FIRST NAME- A
LAST NAME - B
YEAR- 2022
CLASS- FORM 1
STREAM - NORTH
TERM - TERM 1
EXAMS TYPE- CAT 1
(SUBJECTS DONE IN TERM 1)
AGRICULTURE- 80
ARABIC- 70
(SUBJECTS DONE IN TERM 2)
CLICK ADD NEW TERM
YEAR- 2022
CLASS- FORM 1
STREAM- NORTH
TERM- TERM 2
CAT 1
SUBJECTS
AGRICULTURE -20
ARABIC -100
WHEN YOU CLICK
PRINT REPORT FOR THIS STUDENT IN TERM 2, It accumulates all subjects done
1. AGRICULTURE-80
2. ARABIC- 70
3. AGRICULTURE- 20
4. ARABIC- 100
THE SUBJECTS ARE NOT BEING FILTERED SO AS TO GET THE RIGHT REPORT FOR THIS STUDENT IN TERM 2.
Thanks for your help. I really appreciate.

 

[mercystone]

I think there is a way to filter SUBJECTS. Somebody to assist us look at where we are going wrong. Could it be SubjectID? I am defeated for sure.

 

[SHANEMAC51]

I think there is a way to filter SUBJECTS. Somebody to assist us look at where we are going wrong. Could it be SubjectID? I am defeated for sure.

Data entry setup (fixed the main form, filter), 3 subordinate ribbon forms for navigating classes/semesters/topics

I didn't look at the results, PINK FIELDS are NEEDED, they provide navigation through forms

 

[mercystone]

I have changed the subforms to continuous forms and it is working. What is remaining is to transfer to Report forms.

 

[mercystone]

@SHANEMAC51 I have found the solution. Thank you very much. I really appreciate. Thanks for showing me how to do the filter. Thanks.

 

[mercystone]

Thanks everyone

 

[Cronk]

Did you ever change the filter string as suggested in #5? What is your filter string now?

 

[SHANEMAC51]

Did you ever change the filter string as suggested in #5? What is your filter string now?

I often use the code from No. 5, but even more often the following, especially if you need to select not all 4 search fields, but 1-3 fields or part of the value, for example from A+, A, A-, B+, B, B-..... select all B or all D

dim f1,f2
f1 = ""
f2=f_Years & ""
if len(f2)>0 then f1 = f1 & " and Years = " & f2

f2=f_GradeDesc & ""
if len(f2)>0 then f1 = f1 & " and Grade Desc = '" & f2 & "'"

f2=f_TERM & ""
if len(f2)>0 then f1 = f1 & " and TERM ='" & f2 & "'"

f2=f_ExaminationType & ""
if len(f2)>0 then f1 = f1 & " and ExaminationType = '" & f2 & "'"

if len(f1)>0 then
MsgBox mid(f1,5)
DoCmd.OpenReport "REPORT FORMS", acViewReport, , mid(f1,5)
else
DoCmd.OpenReport "REPORT FORMS", acViewReport
endif

 

[SHANEMAC51]

Did you ever change the filter string as suggested in #5? What is your filter string now?

and sometimes it is necessary to check the search conditions for an additional
1- the presence of apostrophes or quotation marks in the field
2-what is contained in the substitution field (code or value)
3- for date - mm/dd/yyyy format
4- an interval of dates or prices/costs is possible and both boundaries are not always known

 

[mercystone]

Thanks @SHANEMAC51 for your help. @Cronk yes this forum has really helped me. I have learned alot from this forum. Thanks for the creator or creators of this forum.

 

[Cronk]

Preumably that was an affirmative that the filter string was changed.