Query to find opposite records in VBA

[Kayleigh]

Hi there,
I currently have a continuous form based on a query which finds all records where criteria is current=true,
I would like to automate a button to display the records where current=false.
What is the most efficient way to do this?
Cheers,
Krayna

 

[MajP]

The simplest way would be to have two queries
qryTrue
qryFalse
qryAll

then you can have an option frame where you set the forms recordsource

something like

select case FrameChoice
case 1
me.recordsource = "qryTrue"
case 2
me.recordsource = "qryFalse"
case 3
me.recordsource = "qryAll"
end select

 

[namliam]

Fairly simple solution, to add to @MajP

Have a PK in your qryTrue... then in your qryFalse you dont need all kinds of difficulties to find nulls etc.... it can simply be:

Select *
From sourceTable(s)
where PK not in ( select PK from qryTrue )

Only troubles you may then run into may be due to relational constrainst enforced by INNER JOIN's

 

[Pat Hartman]

Or a single query with criteria.

Where Current = Forms!yourform!txtValue

 

[Dreamweaver]

I would go with @Pat as saves 2 queries plus I would use @MajP select statement

 

[Kayleigh]

Thank you for your kind replies. I am having trouble as the query does not seem to be finding the current value in the form's text box when it is executed.
Any ideas?

 

[jdraw]

Post the sql of your query for readers to see.

 

[Kayleigh]

SELECT qrylkpStudentName.fldStudentID, qrylkpStudentName.Student, qrylkpStudentName.fldClassID, qrylkpStudentName.Class, qrylkpStudentName.Address, qrylkpStudentName.fldCurrent
FROM qrylkpStudentName
WHERE (((qrylkpStudentName.fldCurrent)=[Forms]![frmStudentList]![txtCurrentValue] Or (qrylkpStudentName.fldCurrent)=True))
ORDER BY qrylkpStudentName.Student;

I have added the WHERE current=true so that it will run but it should not be necessary at all.

 

[jdraw]

What is the sql of qrylkpStudentName?

 

[Kayleigh]

SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldLastName] & " " & [tblstudents].[fldFirstname],[tblstudents].[fldLastName] & " " & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & " " & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & " " & tblParents.fldLastName AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldLastName] & " " & [tblstudents].[fldFirstname] & "  -  " & [fldhousenumber] & " " & [fldstreet]) AS Student1, qrylkpClassName.fldClassID, qrylkpClassName.Class, tblStudents.fldCurrent
FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN (jtblStudentClass LEFT JOIN qrylkpClassName ON jtblStudentClass.fldClassID = qrylkpClassName.fldClassID) ON tblStudents.fldStudentID = jtblStudentClass.fldStudentID
ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldLastName] & " " & [tblstudents].[fldFirstname],[tblstudents].[fldLastName] & " " & [tblStudents].[fldKnownAs]);