Not getting Results!!

[kmkland]

I have created a statement using Not Like, but I am not getting the results that I need. Here's the code:

SELECT [tbl7-10Procedure].Date1Status, [tbl7-10Procedure].Date2Status, [tbl7-10Procedure].Date3Status, [tbl7-10Procedure].Date4Status
FROM [tbl7-10Procedure]
WHERE ((([Date1] & ' ' & [Date1Status]) Not Like "OK*")) OR ((([Date2] & ' ' & [Date2Status]) Not Like "OK*")) OR ((([Date3] & ' ' & [Date3Status]) Not Like "OK*")) OR ((([Date4] & ' ' & [Date4Status]) Not Like "OK*"))
GROUP BY [tbl7-10Procedure].Date1Status, [tbl7-10Procedure].Date2Status, [tbl7-10Procedure].Date3Status, [tbl7-10Procedure].Date4Status;

I want to get results that do not have "OK" in them. But instead, I'm getting all of the results. What should I change?!?
Thanks in advance!!

Rgds,
Kmkland

 

[lespaul]

why are you combining your Date1 field with the Date1Status field?

Have you thought about normalizing your data to make this easier to accomplish?

I want to get results that do not have "OK" in them

specifically which field contains the OK? the Status field?

How's this:

SELECT [tbl7-10Procedure].Date1Status, [tbl7-10Procedure].Date2Status, [tbl7-10Procedure].Date3Status, [tbl7-10Procedure].Date4Status
FROM [tbl7-10Procedure]
WHERE ([Date1Status] Not Like "*OK*") OR ([Date2Status] Not Like "*OK*") OR ([Date3Status] Not Like "*OK*") OR ([Date4Status] Not Like "*OK*");

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kmkland]

Leslie, I had forgotten to remove the combined fields....
Now, I feel quite silly.

Unfortunately, I'm still getting the same results.


Rgds,
Kmkland

 

[SkipVought]

Check your BOOLEAN logic!

WHERE ([Date1Status] Not Like "*OK*") AND ([Date2Status] Not Like "*OK*") AND ([Date3Status] Not Like "*OK*") AND ([Date4Status] Not Like "*OK*");

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]

 

[kmkland]

Then, it will produce no results.

Rgds,
Kmkland

 

[lespaul]

just to clarify, you want to return records where ANY of Date#Status fields DO NOT contain OK or ALL the Date#Status fields DO NOT contain OK.

which do you want?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kmkland]

I need ALL of the fields that do not contain "OK".

Rgds,
Kmkland

 

[lespaul]

so out of the following sample data, which records would you want to see? (if this data doesn't show what you are trying to do, feel free to provide some actual sample data and expected results to help us understand better)

ID Date1Stat Date2Stat Date3Stat Date4Stat
1 Bad Almost OK Good OK
2 Decent Not Good OK Bad
3 OK OK OK OK



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[kmkland]

ID Date1Stat Date2Stat Date3Stat Date4Stat
1 Bad Good
2 Decent Not Good Bad


Rgds,
Kmkland

 

[lespaul]

there's no way to do what you want with the format your data is currently in.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Something like this ?
SELECT ID
, IIf([Date1Status] Like 'OK*', '', [Date1Status]) AS Status1
, IIf([Date2Status] Like 'OK*', '', [Date2Status]) AS Status2
, IIf([Date3Status] Like 'OK*', '', [Date3Status]) AS Status3
, IIf([Date4Status] Like 'OK*', '', [Date4Status]) AS Status4
FROM [tbl7-10Procedure]
WHERE Date1Status & Date2Status & Date3Status & Date4Status Not Like 'OK*OK*OK*OK*'

Sorry Leslie ;-)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

i'm used to it, always know that as soon as I say something can't be done, you'll be right there proving me wrong!

That's why you're the SQL guru!!

have a great day!

les