Solved FILTER BY FORM (1 Viewer)

mercystone

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
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

Code:
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

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
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

Code:
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.

 

mercystone

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
I followed this thread and applied in my database which I used above tables and it works perfectly.
 

SHANEMAC51

Active member
Local time
Today, 16:27
Joined
Jan 28, 2022
Messages
310
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.
 

Attachments

  • st11.png
    st11.png
    78.2 KB · Views: 84
  • st12.png
    st12.png
    47.9 KB · Views: 80
  • student database0222mm.accdb
    1.2 MB · Views: 89

SHANEMAC51

Active member
Local time
Today, 16:27
Joined
Jan 28, 2022
Messages
310
Student IDLast NameFirst NameGenderNAME OF THE SCHOOLKCPEGrade Year IDGrade IDStream IDTerm IDExami nation IDSubject IDMarkSubjectAbrDescription
2CHEP KOECHMILLI CENTFEMALEMARUM BASI SECON DARY SCHOOL430111121450AGRIAgriculture
2-/--/--/--/-43042222660CHEMChemistry
2-/--/--/--/-430111121390ARTArt And Design
2-/--/--/--/-430111121680AVIAAviation
2-/--/--/--/-43011112460BIOBiology
2-/--/--/--/-430111211480AGRIAgriculture
2-/--/--/--/-43011121490BIOBiology
2-/--/--/--/-430111214100BIOBiology

perhaps the table will help you in checking the results
 
Last edited:

mercystone

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
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

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
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

Active member
Local time
Today, 16:27
Joined
Jan 28, 2022
Messages
310
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
 

Attachments

  • ST13.png
    ST13.png
    61.2 KB · Views: 97
  • student database0222mm.accdb
    1.2 MB · Views: 101

mercystone

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
I have changed the subforms to continuous forms and it is working. What is remaining is to transfer to Report forms.
 

mercystone

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
@SHANEMAC51 I have found the solution. Thank you very much. I really appreciate. Thanks for showing me how to do the filter. Thanks.
 

Cronk

Registered User.
Local time
Tomorrow, 00:27
Joined
Jul 4, 2013
Messages
2,770
Did you ever change the filter string as suggested in #5? What is your filter string now?
 

SHANEMAC51

Active member
Local time
Today, 16:27
Joined
Jan 28, 2022
Messages
310
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

Code:
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

Active member
Local time
Today, 16:27
Joined
Jan 28, 2022
Messages
310
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

Member
Local time
Today, 16:27
Joined
Sep 20, 2021
Messages
108
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

Registered User.
Local time
Tomorrow, 00:27
Joined
Jul 4, 2013
Messages
2,770
Preumably that was an affirmative that the filter string was changed.
 

Users who are viewing this thread

Top Bottom