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