Not in (.... being Ignored

[Alkemist]

I'm trying to exclude some results from my query and am using the expression:

Not In ('C*','D037','D038','D039','D04','D040','D041','D042','D043','D044','D045','D046','D047','D048').

Access doesn't throw out any errors but is completely ignoreing the Not in filter.

If I change to Is Not Like "C*" that works...I just didn't want to have one line for each item to filter out....any idea what I'm doing wrong.

I'm a basic user so would appreciate a simple answer ;o)

Cheers..A

 

[jackd]

Can you show us the code where this is used?
What do you think your NOT IN (...) will do?

see http://www.w3schools.com/sql/sql_in.asp

 

[Phildaburn]

Try this:

Not Like 'C*' and Not In ('D037','D038','D039','D04','D040','D041','D042','D043','D044','D045','D046','D047','D048').

 

[Alkemist]

Hia...

Basically I wanted any data with those codes excluded from the results.

It seems the suggestion in writting not like "C*" or not in (....) has worked.

Why couldn't I put all of this in a NOT IN statement, is there a problem with combining these together?

I'll check through the report I have to make sure it does indeed work, but, first glance says it does.

Thanks for your help...Cheers..A

 

[Phildaburn]

"Not In" will not accept a wildcard (* or ? or #). If you want to use the wild card, you could use Not Like "C*" and Not Like "D*", but because you listed all those codes that begin with a "D", I was under the impression that there were perhaps other D-codes that you DO want to include in your results.

 

[Alkemist]

P00...still didn't work...

I'll go to the SQL view and copy and paste that for you to take a look at if you guys n girls wouldn't mind...

 

[Alkemist]

SQL view:

SELECT TOP 100 dbo_tbInpatientEpisodes1011.ProviderCode AS FirstProviderCode, dbo_tbInpatientEpisodes1011.CommissionerCode AS FirstCommCode, dbo_tbInpatientEpisodes1011.PointOfDeliveryRS AS FirstPoD, dbo_tbInpatientEpisodes1011.NHSNumber, dbo_tbInpatientEpisodes1011.EpisodeId, dbo_tbInpatientEpisodes1011.AdmissionDate AS FirstAdmitDate, dbo_tbInpatientEpisodes1011.DischargeDate AS FirstDischargeDate, dbo_tbInpatientEpisodes1011.TreatmentSpecialtyCode AS FirstTFC, dbo_tbInpatientEpisodes1011.TreatmentSpecialtyDescription AS FirstTFCDesc, dbo_tbInpatientEpisodes1011.CBSADerivedHRGCode AS FirstHRG, dbo_tbInpatientEpisodes1011.CBSADerivedHRGDescription AS FirstHRGDesc, dbo_tbInpatientEpisodes1011.CBSADerivedDGVPProcedureCode AS FirstPrimOp, dbo_tbInpatientEpisodes1011.CBSADerivedDGVPProcedureName AS FirstPrimOpDesc, dbo_tbIpDiagnosis1011Relational.DiagnosisCode AS FirstICD10, dbo_tbIpDiagnosis1011Relational.DiagnosisDescription AS FirstICD10Desc INTO Admit_Discharge
FROM (dbo_tbInpatientEpisodes1011 INNER JOIN Trust ON dbo_tbInpatientEpisodes1011.ProviderCode = Trust.Trust) LEFT JOIN dbo_tbIpDiagnosis1011Relational ON dbo_tbInpatientEpisodes1011.EpisodeId = dbo_tbIpDiagnosis1011Relational.EpisodeId
WHERE (((dbo_tbInpatientEpisodes1011.CBSADerivedHRGCode) Not Like "NZ*") AND ((dbo_tbIpDiagnosis1011Relational.DiagnosisCode) Not Like "C*" Or (dbo_tbIpDiagnosis1011Relational.DiagnosisCode) Not In ('D037','D038','D039','D04','D040','D041','D042','D043','D044','D045','D046','D047','D048')) AND ((dbo_tbIpDiagnosis1011Relational.DiagnosisOrder)=1) AND ((dbo_tbInpatientEpisodes1011.IsExcluded)=0) AND ((dbo_tbInpatientEpisodes1011.IsDominant)=1) AND ((dbo_tbInpatientEpisodes1011.IsCosted)=1) AND ((dbo_tbInpatientEpisodes1011.DischargeMethodCode) Not Like "2"));

 

[Phildaburn]

Using the logical OR you've got in your code, let's look at a few examples. If your Diagnosis Code were C5, the first part of your code would reject it, but the second part would include it. If you use the logical AND, anything beginning with a C is rejected as are the list of D codes you've provded. One other comment: your last bit "Not Like "2" does not use a wild card. Did you mean for it to be not equal to? <>?

 

[Alkemist]

Ah ha...

Now I've seen the SQL behind my Access query and followed what you've explained I can see where it was going wrong.

I've changed it to AND and <> because you're right, I'm specifically wanting to exclude "2".

Now it seems to work )

Thanks ever so much for your help....I've learned quite a bit there with how Access works.

Cheers...A

 

[Phildaburn]

WOOHOO!!! Congrats.