SQL Line (Not Like)

[trenton.bachelo]

I have the following code to display what choice I make on a drop down box:

Private Sub cboMajorLocation_AfterUpdate()

Select Case Me.cboMajorLocation.Value

Case 1
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like '*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 2
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'FTM*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 3
Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'CS*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 4
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'PQL*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 5
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Savage*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
Case 6
Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Retail*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"

End Select
End Sub

What I need to know is how to make a statement that you can put in where you want it to display all but take out certain parts of it. Example: On "Case 3" it displays everything starting with "CS*" I want it to not display certain items like "Retail*", & "FTM*" & "PQL*" & "Savage*"

Any help for this statement would be great thanks

 

[yhgtbfk]

I think the answer is in your question.

AND fieldName NOT LIKE 'Retail*' AND fieldName NOT LIKE 'FTM*' (etc...)

 

[Pat Hartman]

You don't need all that code. All you need is a parameter query.

Select ....
From ...
Where WHERE (tblHardware.Location Like Forms!YourForm!YourCombo & "*" or IsNull(Forms!YourForm!YourCombo) AND (tblHardware.Type = 'PC')
Order By ...

When you want to exclude a list of items the easiest syntax to work with is:

Where YourField Not In("val1", "val2", "val3", "val4");

FYI, make sure you actually need to use the Like operator. It is far less efficient than the = operator. You only need to use LIKE if the search value contains only a partial string. If the search value is always complete (which yours seems to be since it is selected from a combo) then change the code to use the =.

 

[trenton.bachelo]

One more question

Alright I got that part figured out with using the Not Like. One more question that I have is when I am viewing all of the locations using the '*', there are some without any location, and then it takes those away. What I need to know is how to make those appear too. How to actually make everything apprear, even the empty fields.

 

[Pat Hartman]

Whenever you want null values to be included, you need to specifically request them.

Where (.....) OR MyField Is Null;