Solved FILTER BY FORM

[mercystone]

Hello everyone. This forum has really helped me. I really appreciate. Kindly look at the attached database. I am trying to filter records by form. Look at the form "frmStudents" and form "print Report forms"
When you filter from drop-down for example
Year- 2020
Class- form1
Stream-north
Term- Term 1
Examination Type- CAT 1
SUBJECTS SHOWN DONE ARE 8

When you filter again
Year-2020
Class-form 1
Stream- north
Term- Term 2
Examination Type- CAT 1
STILL SUBJECTS SHOWN DONE ARE 8
(TERM 1-8 AND TERM 2-8) Meaning the Subjects are not being filtered as being done by Students.
Frm Students has a problem with
Print Report for this Student- it doesn't filter.
Kindly assist me please.

 

[Gasman]

How do you know Term2 is not correct?
Walk though your code with F8 and inspect the variables.
Debug.print the filter string to see what you have, not what you think you have.

 

[mercystone]

How do you know Term2 is not correct?
Walk though your code with F8 and inspect the variables.
Debug.print the filter string to see what you have, not what you think you have.

I am still new to Ms access. This forum has really helped me. Let me try though I don't know how to use debug.print

 

[Gasman]

Type it in the code after you set the filter.
Debug.print myformfilter
Then look in the immediate window for the output.
Also useful for posting here when things do not work, like possibly now?

 

[SHANEMAC51]

This forum has really helped me. I really appreciate. Kindly look at the attached database. I am trying to filter records by form. Look at the form "frmStudents" and form "print Report forms"

you are forming the filter string incorrectly

Filter = "Years = " & Years
Filter = Filter & " and Grade Desc = '" & GradeDesc & "'"
Filter = Filter & " and TERM ='" & TERM & "'"
Filter = Filter & " and ExaminationType = '" & ExaminationType & "'"
MsgBox Filter
DoCmd.OpenReport "REPORT FORMS", acViewReport, , Filter

 

[mercystone]

you are forming the filter string incorrectly

Filter = "Years = " & Years
Filter = Filter & " and Grade Desc = '" & GradeDesc & "'"
Filter = Filter & " and TERM ='" & TERM & "'"
Filter = Filter & " and ExaminationType = '" & ExaminationType & "'"
MsgBox Filter
DoCmd.OpenReport "REPORT FORMS", acViewReport, , Filter

Still I get 8 Subjects
TERM 1
1. AGRICULTURE-50
2. CHEMISTRY- 60
3. ART AND DESIGN- 90
4. AVIATION- 80
5. BIOLOGY- 60
6. AGRICULTURE- 80
7. BIOLOGY- 90
8. BIOLOGY- 100
IF YOU CHECK AT THE FORM:
YEARS-2020
CLASS- FORM 1
STREAM- NORTH
TERM- TERM 1
Examination type- CAT 1
SHOULD BE:
1. AGRICULTURE-50
2. ART AND DESIGN- 90
3. AVIATION- 80
4. BIOLOGY- 60
SUBJECTS ARE NOT BEING FILTERED.
I don't where I am wrong. Could it be queries. Kindly assist me.

 

[mercystone]

Kindly check the subreport. I am defeated for sure

 

[mercystone]

I don't know if the problem lies in query 3 because the Subjects are not filtered meaning a student who sits for exams in term 1 and term 2 and term 3 all subjects entered will be printed even if you filter. Please

 

[SHANEMAC51]

compare filter strings 1, works for all
2 does not work for one student (incorrect syntax)

 

[mercystone]

compare filter strings 1, works for all
2 does not work for one student (incorrect syntax)
View attachment 98447

Oh ok. I guess there is a way to filter one Student per SUBJECTS DONE. Mmmmmmm. That could be of help

 

[SHANEMAC51]

Oh ok. I guess there is a way to filter one Student per SUBJECTS DONE. Mmmmmmm. That could be of help

decide on the statements, they are different in your tables and forms (and even for the same table).... this is the reason for your mistakes

 

[SHANEMAC51]

and of course read about the complex filter

filter ="year=2020 and school='school 1' and city='boston'"

 

[mercystone]

and of course read about the complex filter

filter ="year=2020 and school='school 1' and city='boston'"

Getting confused

 

[mercystone]

Thanks for your help though I can't figure out the problem from the table

 

[SHANEMAC51]

Thanks for your help though I can't figure out the problem from the table

you need to read about the productions and do them the same way in the tables and the format now they are different for you, therefore it is chosen incorrectly in one student * obvious lies) and it is unlikely to form correctly for all students (although it works)

 

[mercystone]

It is my wish to make the best database. If there is a problem help me please.

 

[mercystone]

I will redo it again

 

[SHANEMAC51]

It is my wish to make the best database. If there is a problem help me please.

I am unlikely to be able to help you - I do not know English, my messages are translated by Google,
you need to either study some textbook yourself or look for an English-speaking consultant

 

[mercystone]

I am unlikely to be able to help you - I do not know English, my messages are translated by Google,
you need to either study some textbook yourself or look for an English-speaking consultant

You are very funny but I appreciate your help.

 

[SHANEMAC51]

It is my wish to make the best database. If there is a problem help me please.

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