Multi-variable query (1 Viewer)

[liddlem]

Hi All
Before putting my problem out there, you n eed to be aware that I am working in a very different (technically challenged) environment which does not necessarily lend itself to automation in the 'ideal' world scenario.
Hence the following (partly manual) process.

In my Stu_Student table, a field (ID_Att_Code) stores the most recent attendance status for a given student.
As expected, we need to to record attendance each day.

On the ground, the teacher get a PRINTED class list [or subject list which might comprise multiple classes]
This list reflects the last known Attendance code for each student (I.E. Whatever code was assigned at the last roll call).
If the code changes for the student, (eg The student not in class when they should be) then the teacher manually (in ink) changes the code on the page.
That page is then sent to the Admin Officer (AO) to record the attendance.

In order to make life a little easier for this person, I have created a 'bulk update' form.
The problem is that the parameters change, depending on which report is used (Class list/Subject list)
So, The AO might choose to filter by Class name, and/or Year level and/or Attendance code.

The following is the underlying query that the form is based on. But its to complect to be calculated.

SELECT 
 dbo_STU_Student.ID_Student,
 dbo_STU_Student.ID_Campus,
 dbo_STU_Student.FORM_Name,
 dbo_STU_Student.ID_Att_Code,
 dbo_STU_Student.ID_YrLevel,
 dbo_STU_Student.FCE,  'Flag that indicates Future, Current or Ex Student
 TMP_ATT_History_for_Date.ATT_Date,  'I create a temp table to check if attendance has already been recorded for the student/date
 IIf(IsNull([Sq]),1,[sq]) AS Seq     'If applicable - This counts the number if times that attendance has been recorded for this student/date
FROM dbo_STU_Student LEFT JOIN TMP_ATT_History_for_Date ON dbo_STU_Student.ID_Student = TMP_ATT_History_for_Date.ID_Student
'This where I am having trouble
WHERE (((dbo_STU_Student.FORM_Name)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_FormID]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_FormID]")) AND ((dbo_STU_Student.FCE)="C"))
OR (((dbo_STU_Student.ID_Att_Code)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_AttCode]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_AttCode]")) AND ((dbo_STU_Student.FCE)="C")) 
OR (((dbo_STU_Student.ID_YrLevel)=IIf(IsNull([Forms]![FRM_ATT_BulkUpdate]![FILTER_YrLevel]),",[Forms]![FRM_ATT_BulkUpdate]![FILTER_YrLevel]")) AND ((dbo_STU_Student.FCE)="C"))
'End of trouble
ORDER BY dbo_STU_Student.ID_Campus,
dbo_STU_Student.ID_FORMID,
Trim([NAME_Surname]) & ", " & Trim([NAME_First]);

Do you know of a better way to handle this situation?

 

[Ranman256]

dont put lots of IIFs into sql. you want to do your Ifs in a form.
Then the FORM builds the sql 'where' clause.

Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'apply the sql to the form

sSql = "SELECT * FROM tblCompany WHERE " & sWhere


     'save the sql as a qry or open the sql
set qdf = currentdb.querdefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

 

[isladogs]

Do you know of a better way to handle this situation?

Well yes ... get the teacher to register the attendance on a computer themselves (using a form similar to this)

Doing this means the attendance officer can get on with following up absences instead

But I assume this is not possible in your technically challenged environment

 

[liddlem]

Thanks Ranman - I like your thinking. Will give this a go and see what happens.

Ridders : That was my first port of call too.