Query criteria Expression for Not Like Is ina table field (1 Viewer)

[jyadayada]

I've previously built queries where you filter the results by pointing the criteria of a given field to a list of inclusions, I can't remember the exact syntax but something along the lines of



Is In [FieldName].[Table Name]


I'm need to write this into the design view criteria of the field ina query, and i want it to exclude items on a list, so without any syntax, the equivalent of


Not Like [FieldName] [Table Name]


filtering the query results to exclude items that are listed in the [Table Name file]


what would the expression criteria syntax be for this?

 

[plog]

IN and LIKE are 2 different comparison methods, they do different things. Here's a link to how IN works:

https://www.w3schools.com/sql/sql_in.asp

And here is LIKE:

https://www.w3schools.com/sql/sql_like.asp

Both support NOT:

https://www.w3schools.com/sql/sql_and_or.asp

 

[jyadayada]

I appreciate the links and they are bookmarked but i'm so new to access that the SQL view of queries doesn't fully make sense to me yet, I build them in the front design view


Is there criteria expression text i can put into the field in design view that will return the query without the records that are referenced in a seperate field list?

 

[plog]

Yes, you put your criteria on the criteria line of the field you want to apply the criteria to in Design view.

For example, if you wanted to exclude sales people from a query by name, you would do this under the SalesPersonFirstName field:

NOT IN ("Tim", "Steve", "Larry")

If you wanted to exclude every sales person whose last name began with an M, you would put this under the SalesPersonLastName field:

NOT LIKE "M*"


If you want help specific to your issue, you are going to have to try something, then post back what you tried and an explanation of how it didn't work.

 

[jyadayada]

I have working



Not In ("Field Value 1","Field Value 2","Field Value 3","Field Value 4")


but I'm building this to be worked by my successor and the exclusions could run into the thousands, more likely hundreds but a long list to express as above


this is why i'm lookinh for a Not In [Point to a single field table list of matching field values] option if it's possible, without having a head for excel

 

[Minty]

You can use another query in an IN () construct as a sub query.

NOT IN ((SELECT TheFieldYouWant from YourTable WHERE Criteria Goes Here))

 

[jyadayada]

I've built a query that I was going to point to a helper table of just the field values to be excluded so the criteria is covered by that look up list


so would


Not In ( (SELECT [FieldName] from [TableName])) work?

 

[Minty]

You can do that or you could join that query (QryA) to your original query and left join to only include null values in QryA

It sounds a bit odd but is a common method of excluding records in queries.

 

[plog]

Yes, but I advise against that. Instead, make that SELECT a whole new query. Name it 'sub1'. Then bring it into your existing query, JOIN on FieldName to your existing table. Change that JOIN to show all from your existing table. Then bring down FieldName from sub1 and put 'Is Null' in the criteria.

Then when things go sideways or you need to modify it, you can simply open up sub1 to verify it has the data you think it has.

 

[jyadayada]

Fantastic! that works a treat


In case there aren't thousands of exclusions and the Not In("Value1","Value2") route is taken, i don't suppose you know off the top of your head the maximun limit on number of values is


thanks again!

 

[plog]

I think you bump up against a character limit not a value limit. 255 characters would be my guess. But after even a few values the subquery method is more practical.

 

[Minty]

If you build a Not IN list in VBA (Which is a common way of building results from a listbox) then you run out of characters but not until it's very long. (4000 +characters ?)

But as Plog and I stated the subquery / Null join is much more efficient.

 

[Minty]

Actually found this about character limits via another thread;

Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control. - 32,750

 

[plog]

Ugh the left side of my brain got a shiver reading that.

However, that sounds like a good contest. Who can post the longest single SELECT Query they have come across in the wild? Rules: Can have only 1 SELECT; must be actual code used to do something (e.g not specifically written for this contest),

 

[Minty]

I had one that used to collect job numbers into an IN() and it went really long when we had a massive update by a customer.

I seem to remember it was way too big for the immediate window when I was debugging it ...

 

[isladogs]

As a starting point, here's an INSERT statement that's 2367 characters excluding spaces

INSERT INTO ArchivedTeachers ( Surname, Initials, Forename, Title, TeacherID, Active, DOB, Protect, 
         UserName, [Password], Faculty, UserCode, EMail, LastDate, Gender, Telephone, Mobile, AddressBlock, 
         AddressLine, SENPermission, LSW, PastoralPermission, PastoralManager, Teacher, SupplyTeacher, LT, YearTeam, 
         HOD, Admin, LSA, CP, CPManager, Site, Cleaner, SMSA, SDAManager, Visitor, StudentInfoEditor, TgtEditor, RRManager, SENWaveEditor, 
         ITTstudent, EditStaffInfo, EditAppointments, EditCalendar, RemoteAccess, ExclusionEditor, ExclusionRoomEditor, 
         SchoolDetention, JobRole, Ext, DDI, PhotoPath, PhotoAvailable, ImagePath, Notes, Imported, ShowSDAHomePage, 
         Show64bitOffice, EALManager, AttendanceManager, EditLessonRegisters, EditSessionRegisters, ShowUpdateReminders, ShowHomePageTips) 
         SELECT Teachers.Surname, Teachers.Initials, Teachers.Forename, Teachers.Title, Teachers.TeacherID, Teachers.Active, 
         Teachers.DOB , Teachers.Protect, Teachers.UserName, Teachers.Password, Teachers.Faculty, Teachers.UserCode, 
         Teachers.Email, Teachers.LastDate, Teachers.Gender, Teachers.Telephone, Teachers.Mobile, Teachers.AddressBlock, 
         Teachers.AddressLine, Teachers.SENPermission, Teachers.LSW, Teachers.PastoralPermission, Teachers.PastoralManager, 
         Teachers.Teacher, Teachers.SupplyTeacher, Teachers.LT, Teachers.YearTeam, Teachers.HOD, Teachers.Admin, 
         Teachers.LSA, Teachers.CP, Teachers.CPManager, Teachers.Site, Teachers.Cleaner, Teachers.SMSA, Teachers.SDAManager, 
         Teachers.Visitor, Teachers.StudentInfoEditor, Teachers.TgtEditor, Teachers.RRManager, Teachers.SENWaveEditor, Teachers.ITTstudent, 
         Teachers.EditStaffInfo, Teachers.EditAppointments, Teachers.EditCalendar, Teachers.RemoteAccess, Teachers.ExclusionEditor, 
         Teachers.ExclusionRoomEditor, Teachers.SchoolDetention, Teachers.JobRole, Teachers.Ext, Teachers.DDI, 
         Teachers.PhotoPath, Teachers.PhotoAvailable, Teachers.ImagePath, Teachers.Notes, Teachers.Imported, Teachers.ShowSDAHomePage, 
         Teachers.Show64bitOffice, Teachers.EALManager, Teachers.AttendanceManager, Teachers.EditLessonRegisters, Teachers.EditSessionRegisters, 
         Teachers.ShowUpdateReminders, Teachers.ShowHomePageTips, Teachers.MessageManager, Teachers.MessageUser, 
         Teachers.AbsenceMessageUser, Teachers.DetentionMessageUser 
         FROM Teachers LEFT JOIN ArchivedTeachers ON Teachers.TeacherID = ArchivedTeachers.TeacherID 
         WHERE (((Teachers.Surname)<>'') AND ((Teachers.Forename)<>'') AND ((Teachers.Protect)=False) 
         AND ((Teachers.UserName)<>'') AND ((Teachers.Imported)=False) AND ((ArchivedTeachers.TeacherID) Is Null));

I can probably find far longer than that if I try ....
Most of my long SQL statements are caused where I need all fields except one e.g. the autonumber field
However I'm usually restricted by the maximum limit on line continuations

Also just noticed a procedure of 71949 characters (excluding spaces) but I'm sure someone else can beat that.

The attached screenshot shows the overall stats (from when I last checked) for what is probably my largest Access FE in terms of database objects

 

[Minty]

I'm impressed you knocked all that lot up in 62 seconds...

 

[Minty]

I found a select in a SQL stored procedure I wrote, that was one of two to do a really awkwards accounts package look up.
Each one is 1900 + characters that are then joined, and then queried again 7505 characters in total

Not one single query though

 

[isladogs]

I'm impressed you knocked all that lot up in 62 seconds...

Ah but I've slowed down a bit in the last couple of years