I have a form based on this query:
SELECT Students.LastSe rDT, OtherInfo.Serve d, OtherInfo.HSGra dYr,
OtherInfo.Activ ePart, OtherInfo.Serve d, Students.SSN, [LastNM] & ", " &
[FirstNM] & " " & [MI] AS Name, Students.LastNM , Students.FirstN M,
Students.MI, Students.DOB, Students.Gender CD, Students.Ethnic ityCD,
Students.Eligib ilityCD, Students.UBInit iative, Students.NCESSc hID,
Students.ProjEn tryDT, Students.ProjRe EntDT, Students.LastSe rDT,
Students.Reason , Students.PartCD , Students.PartLV , Students.NeedCD ,
Students.Assess CD, Students.PSAT, Students.PLAN, Students.EnterG radeLV,
Students.EndGra deLV, Students.EntryG PAScale, Students.CumGPA Entry,
Students.EndGPA Scale, Students.HSGPA1 , Students.HSGPA2 , Students.CollEx amCD,
OtherInfo.NCESS chNM, OtherInfo.Middl eNM, OtherInfo.Curre ntGradeLV,
OtherInfo.Curre ntHSNM, Students.Pictur ePath
FROM Students LEFT JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
WHERE (((Students.Las tSerDT) Is Null)) OR (((OtherInfo.Se rved)=Yes)) OR
((([Forms]![frmStudents]![CheckCriteria])=Yes))
ORDER BY Students.LastNM , Students.FirstN M;
If [Forms]![frmStudents]![CheckCriteria]=No then it shows records WHERE
Students.LastSe rDT Is Null OR OtherInfo.Serve d=Yes. (This is the default
when the form is opened.) If [Forms]![frmStudents]![CheckCriteria]=Yes then
it shows every record. It works just like I wanted.
When I click the CheckCriteria checkbox this is called:
Private Sub CheckCriteria_C lick()
'if checkbox = yes then shows all students, else shows current/active
Me.Requery
Me.ComboStudent .Requery
ShowName = ComboStudent.Co lumn(2)
End Sub
The ComboStudent combobox gets its information from the same query so that
it shows the correct records.
However, now the user wants the option of showing students with a particular
HSGradYr. I have added a textbox called txtGradYear to my form for the user
to enter a year, but I don't know where to go from here.
I'm basically wanting this one query to act in 3 different ways.
1. WHERE Students.LastSe rDT Is Null OR OtherInfo.Serve d=Yes
2. WHERE [Forms]![frmStudents]![CheckCriteria]=Yes
3. WHERE OtherInfo.HSGra dYr=[forms]![frmStudents]![txtGradYear]
And I want the ComboStudent to display the appropriate entries for each
WHERE.
I've got it working the first 2 ways, but I cannot figure out how to get the
3rd way to work. I tried:
WHERE (Students.LastS erDT Is Null OR OtherInfo.Serve d=Yes) OR
([Forms]![frmStudents]![CheckCriteria]=Yes) OR
(OtherInfo.HSGr adYr=[forms]![frmStudents]![txtGradYear])
but I didn't get JUST the records where
OtherInfo.HSGra dYr=[forms]![frmStudents]![txtGradYear] which makes sense.
Can anyone help me get what I need?
Thanks in advance,
Debbie
SELECT Students.LastSe rDT, OtherInfo.Serve d, OtherInfo.HSGra dYr,
OtherInfo.Activ ePart, OtherInfo.Serve d, Students.SSN, [LastNM] & ", " &
[FirstNM] & " " & [MI] AS Name, Students.LastNM , Students.FirstN M,
Students.MI, Students.DOB, Students.Gender CD, Students.Ethnic ityCD,
Students.Eligib ilityCD, Students.UBInit iative, Students.NCESSc hID,
Students.ProjEn tryDT, Students.ProjRe EntDT, Students.LastSe rDT,
Students.Reason , Students.PartCD , Students.PartLV , Students.NeedCD ,
Students.Assess CD, Students.PSAT, Students.PLAN, Students.EnterG radeLV,
Students.EndGra deLV, Students.EntryG PAScale, Students.CumGPA Entry,
Students.EndGPA Scale, Students.HSGPA1 , Students.HSGPA2 , Students.CollEx amCD,
OtherInfo.NCESS chNM, OtherInfo.Middl eNM, OtherInfo.Curre ntGradeLV,
OtherInfo.Curre ntHSNM, Students.Pictur ePath
FROM Students LEFT JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
WHERE (((Students.Las tSerDT) Is Null)) OR (((OtherInfo.Se rved)=Yes)) OR
((([Forms]![frmStudents]![CheckCriteria])=Yes))
ORDER BY Students.LastNM , Students.FirstN M;
If [Forms]![frmStudents]![CheckCriteria]=No then it shows records WHERE
Students.LastSe rDT Is Null OR OtherInfo.Serve d=Yes. (This is the default
when the form is opened.) If [Forms]![frmStudents]![CheckCriteria]=Yes then
it shows every record. It works just like I wanted.
When I click the CheckCriteria checkbox this is called:
Private Sub CheckCriteria_C lick()
'if checkbox = yes then shows all students, else shows current/active
Me.Requery
Me.ComboStudent .Requery
ShowName = ComboStudent.Co lumn(2)
End Sub
The ComboStudent combobox gets its information from the same query so that
it shows the correct records.
However, now the user wants the option of showing students with a particular
HSGradYr. I have added a textbox called txtGradYear to my form for the user
to enter a year, but I don't know where to go from here.
I'm basically wanting this one query to act in 3 different ways.
1. WHERE Students.LastSe rDT Is Null OR OtherInfo.Serve d=Yes
2. WHERE [Forms]![frmStudents]![CheckCriteria]=Yes
3. WHERE OtherInfo.HSGra dYr=[forms]![frmStudents]![txtGradYear]
And I want the ComboStudent to display the appropriate entries for each
WHERE.
I've got it working the first 2 ways, but I cannot figure out how to get the
3rd way to work. I tried:
WHERE (Students.LastS erDT Is Null OR OtherInfo.Serve d=Yes) OR
([Forms]![frmStudents]![CheckCriteria]=Yes) OR
(OtherInfo.HSGr adYr=[forms]![frmStudents]![txtGradYear])
but I didn't get JUST the records where
OtherInfo.HSGra dYr=[forms]![frmStudents]![txtGradYear] which makes sense.
Can anyone help me get what I need?
Thanks in advance,
Debbie
Comment