Multiply Parameter Queries!

[atiq]

i have a Query where i have Criteria for two fields as the following:

Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

Occasionally, staff may just want all incidents (records) to be shown for the selected StudentSurname and so, just select the surname and leave the date fields blank.

i tried the following:
Field :Date of Incident
Crirteria: [Forms]![frmSearchStudent&Date]![cboStartDate] And [Forms]![frmSearchStudent&Date]![cboEndDate]
Or: [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null And [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null

Field: StudentSurname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]

then when i tested this by leaving the date fields blank and selecting a valid student surname it simply returned all incidents (records) for ALL students not the selected one.

is it possible to achieve this? if so, could someone please help!

 

[Dennisk]

I think it should be like this
[Forms]![frmSearchStudent&Date]![cboStartDate] AND ( [Forms]![frmSearchStudent&Date]![cboEndDate] or [Forms]![frmSearchStudent&Date]![cboEndDate] is null)

 

[atiq]

i tried that but it doesnt seem to work neither do i get any error message.

 

[RoyVidar]

Perhaps if you post the SQL of your query - from the view meny, select SQL.

 

[RoyVidar]

I was sent the following SQL as a PM - please respond in the fora

SELECT tblIncident.IncidentID, tblIncident.DateOfIncident, tblIncident.Period, tblIncident.Covering, tblIncident.Location, tblIncident.StaffID, tblIncident.BehaviourID, tblIncident.DateOfDetention, tblIncident.TimeOfDetention, tblIncident.DescriptionOfIncident, tblIncident.Witness, tblIncident.ActionID, tblStudent.StudentSurname
FROM tblStudent INNER JOIN tblIncident ON tblStudent.StudentID = tblIncident.StudentID
WHERE (((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboStartDate] And ((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboEndDate] Or [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null)) AND ((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0]));

Tweaked a bit, with some aliasing etc, but I'm not sure I understand this query. The incidentdate is supposed to be both the selected start date and the selceted end date? Shouldn't it be between those dates? For this to work, you have to select the same date for both start and and end, I'm changing to OR (else you wouldn't need two dates at all, only one). For the Is Null part, it seems you've only used that for the end date, only, it will need to be on both.

SELECT 
	i.IncidentID, i.DateOfIncident, i.Period, i.Covering, i.Location, 
	i.StaffID, i.BehaviourID, i.DateOfDetention, i.TimeOfDetention, 
	i.DescriptionOfIncident, i.Witness, i.ActionID, s.StudentSurname
FROM 
	tblStudent s INNER JOIN tblIncident i ON s.StudentID = i.StudentID
WHERE 
	s.StudentSurname=[Forms]![frmSearchStudent&Date]![Combo0] AND

	((i.DateOfIncident=[Forms]![frmSearchStudent&Date]![cboStartDate] OR
	[Forms]![frmSearchStudent&Date]![cboStartDate] Is Null) [b]OR[/b]

	(i.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboEndDate] Or
 	[Forms]![frmSearchStudent&Date]![cboEndDate] Is Null))

I think, if you change the highlighted OR to AND, it will be more true to the original statement (the incident date must be equal to both the selected start date and the selected end date), but then I don't think there should be a need for more than one date selection control/combo. Try it.

In the query interface, it might look like this (at least when trying to design it, Acces will mess it up to quite some extent afterwards)

For the surname
Criteria: [Forms]![frmSearchStudent&Date]![Combo0]
Or: [Forms]![frmSearchStudent&Date]![Combo0]

For the date
Criteria: [Forms]![frmSearchStudent&Date]![cboStartDate] or [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null
or: [Forms]![frmSearchStudent&Date]![cboEndDate] or [Forms]![frmSearchStudent&Date]![cboEndDate] Is Null

Here's also playing with between I e for instance

WHERE 
	s.StudentSurname=[Forms]![frmSearchStudent&Date]![Combo0] AND

	((i.DateOfIncident[b]>=[/b][Forms]![frmSearchStudent&Date]![cboStartDate] OR
	[Forms]![frmSearchStudent&Date]![cboStartDate] Is Null) AND

	(i.DateOfIncident)[b]<=[/b][Forms]![frmSearchStudent&Date]![cboEndDate] Or
 	[Forms]![frmSearchStudent&Date]![cboEndDate] Is Null))

Disclaimer, none of this has run on a computer, it's just tweaked a bit, but I think if I've gotten the parentheses reasonably correct, it should work.

 

[atiq]

i tried the following SQL code as i instructed:
SELECT tblIncident.DateOfIncident, tblIncident.Period, tblIncident.Location, tblIncident.DescriptionOfIncident, tblIncident.DateOfDetention, tblIncident.TimeOfDetention, tblAction.Decription, tblBehaviourType.BehaviourType, tblStaff.StaffSurname, tblStudent.StudentSurname, tblStudent.StudentForename, tblStaff.Title, [Title] & " " & [StaffSurname] AS StaffFullName, [StudentForename] & " " & [StudentSurname] AS StudentFullName
FROM tblStudent INNER JOIN (tblBehaviourType INNER JOIN (tblAction INNER JOIN (tblStaff INNER JOIN tblIncident ON tblStaff.[Staff ID] = tblIncident.StaffID) ON tblAction.ActionID = tblIncident.ActionID) ON tblBehaviourType.BehaviourID = tblIncident.BehaviourID) ON tblStudent.StudentID = tblIncident.StudentID
WHERE (((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboStartDate] Or [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null) AND ((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0])) OR (((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboStartDate] Or [Forms]![frmSearchStudent&Date]![cboStartDate] Is Null) AND ((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0]));


but wen i tested it by leave the start and end date fields but selected a valid student surname, it came with an error message. then when i opened the query in design it had automatically changed to the following:

SELECT tblIncident.DateOfIncident, tblIncident.Period, tblIncident.Location, tblIncident.DescriptionOfIncident, tblIncident.DateOfDetention, tblIncident.TimeOfDetention, tblAction.Decription, tblBehaviourType.BehaviourType, tblStaff.StaffSurname, tblStudent.StudentSurname, tblStudent.StudentForename, tblStaff.Title, [Title] & " " & [StaffSurname] AS StaffFullName, [StudentForename] & " " & [StudentSurname] AS StudentFullName
FROM tblStudent INNER JOIN (tblBehaviourType INNER JOIN (tblAction INNER JOIN (tblStaff INNER JOIN tblIncident ON tblStaff.[Staff ID] = tblIncident.StaffID) ON tblAction.ActionID = tblIncident.ActionID) ON tblBehaviourType.BehaviourID = tblIncident.BehaviourID) ON tblStudent.StudentID = tblIncident.StudentID
WHERE (((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboStartDate]) AND ((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0])) OR (((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0]) AND (([Forms]![frmSearchStudent&Date]![cboStartDate]) Is Null)) OR (((tblIncident.DateOfIncident)=[Forms]![frmSearchStudent&Date]![cboEndDate]) AND ((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0])) OR (((tblStudent.StudentSurname)=[Forms]![frmSearchStudent&Date]![Combo0]) AND (([Forms]![frmSearchStudent&Date]![cboEndDate]) Is Null));

Any ideas how i can sort this problem?

 

[RoyVidar]

I'm sorry, this is both different from what you sent me on PM, and from what I suggested, in addition to the extra tables, there are no alias, and

Could we concentrate on one, please.

What happens if you copy my suggestion, and paste into the SQL view of a new query?

Please also state what errormessage you're receiving