Solved FILTER BY FORM (1 Viewer)

mercystone

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

Attachments

  • student database.zip
    190.6 KB · Views: 253
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
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

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

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
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

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

Member
Local time
Today, 18:08
Joined
Sep 20, 2021
Messages
108
you are forming the filter string incorrectly
Code:
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

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

Active member
Local time
Today, 18:08
Joined
Jan 28, 2022
Messages
310
compare filter strings 1, works for all
2 does not work for one student (incorrect syntax)
sd11.png
 

Attachments

  • sd13.png
    sd13.png
    31.6 KB · Views: 276

SHANEMAC51

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

Active member
Local time
Today, 18:08
Joined
Jan 28, 2022
Messages
310
and of course read about the complex filter

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

mercystone

Member
Local time
Today, 18:08
Joined
Sep 20, 2021
Messages
108
Thanks for your help though I can't figure out the problem from the table
 

SHANEMAC51

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

Member
Local time
Today, 18:08
Joined
Sep 20, 2021
Messages
108
It is my wish to make the best database. If there is a problem help me please.
 

SHANEMAC51

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

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

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

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
 

Attachments

  • cx11.png
    cx11.png
    57 KB · Views: 205
  • cx12.png
    cx12.png
    20.9 KB · Views: 266

Users who are viewing this thread

Top Bottom