• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Query to find opposite records in VBA (1 Viewer)

Krayna

New member
Local time
Today, 10:01
Joined
Sep 24, 2020
Messages
22
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, 05:01
Joined
May 21, 2018
Messages
3,489
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, 11:01
Joined
Aug 11, 2003
Messages
11,664
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
 

Krayna

New member
Local time
Today, 10:01
Joined
Sep 24, 2020
Messages
22
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?
 

Krayna

New member
Local time
Today, 10:01
Joined
Sep 24, 2020
Messages
22
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.
 

Krayna

New member
Local time
Today, 10:01
Joined
Sep 24, 2020
Messages
22
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