Query to find opposite records in VBA (1 Viewer)

Kayleigh

Member
Local time
Today, 11:03
Joined
Sep 24, 2020
Messages
706
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

You've got your good things, and you've got mine.
Local time
Today, 07:03
Joined
May 21, 2018
Messages
8,463
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

The Mailman - AWF VIP
Local time
Today, 12:03
Joined
Aug 11, 2003
Messages
11,696
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:
Code:
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
 

Kayleigh

Member
Local time
Today, 11:03
Joined
Sep 24, 2020
Messages
706
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?
 

Kayleigh

Member
Local time
Today, 11:03
Joined
Sep 24, 2020
Messages
706
SQL:
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.
 

Kayleigh

Member
Local time
Today, 11:03
Joined
Sep 24, 2020
Messages
706
SQL:
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]);
 

Users who are viewing this thread

Top Bottom