Query only working with 2 items in criteria

[Linda RQ]

Using Access 2000. I have a query with patient location as a field and I
have entered item number 1 in the criteria in the query grid. This filters
out any patients with a room number of ms28 or patients in NICU. If I add
more to the list..picu and cv, it gives me no records. I tried replacing
the And with or and that didn't work either. How can I put filter out more
than 2 units?

1. Not Like "ms**" And <>"nicu"

2. Not Like "ms**" And <>"nicu" And <>"picu" And Like "cv**"

Thanks,
Linda

 

[John Vinson]

Using Access 2000. I have a query with patient location as a field and I
have entered item number 1 in the criteria in the query grid. This filters
out any patients with a room number of ms28 or patients in NICU. If I add
more to the list..picu and cv, it gives me no records. I tried replacing
the And with or and that didn't work either. How can I put filter out more
than 2 units?

1. Not Like "ms**" And <>"nicu"

2. Not Like "ms**" And <>"nicu" And <>"picu" And Like "cv**"

Eh? If the value is LIKE "cv*" then a) you don't need the second
asterisk, * means match any length string; and b) you don't need the
other criteria, since "ms", "nicu" and "picu" are all UNlike "cv*".

If this doesn't make sense, please open the query in SQL view. "Not"
logic can be confusing, and it may be easier to parse the query in
SQL; parenthesis nesting is important and hard to visualize in the
grid.

John W. Vinson[MVP]

 

[LMB]

Eh? If the value is LIKE "cv*" then a) you don't need the second
asterisk, * means match any length string; and b) you don't need the
other criteria, since "ms", "nicu" and "picu" are all UNlike "cv*".

If this doesn't make sense, please open the query in SQL view. "Not"
logic can be confusing, and it may be easier to parse the query in
SQL; parenthesis nesting is important and hard to visualize in the
grid.

John W. Vinson[MVP]

SELECT DISTINCTROW [RT-FLOW].[RM#], [RT-FLOW].[BD#], [RT-FLOW].[ADM NUMBER],
[RT-FLOW].[LAST NAME], [RT-FLOW].[FIRST NAME], [RT-FLOW].THERAPY,
[RT-FLOW].FREQUENCY, [RT-FLOW].MEDS, [RT-FLOW].VENT, [RT-FLOW].[ORDER DATE],
[RT-FLOW].OrderTime, [RT-FLOW].[RM#], [RT-FLOW].ysnTDP,
[RT-FLOW].ysnOrderRenewal
FROM [RT-FLOW]

WHERE ((([RT-FLOW].[RM#]) Not Like "ms**" And ([RT-FLOW].[RM#])<>"nicu") AND
(([RT-FLOW].VENT) Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

This works, it filters out all the msicu and nicu patients but I can't add
other areas to exclude.

I tried this below...
WHERE ((([RT-FLOW].[RM#]) Not Like "ms*" And ([RT-FLOW].[RM#])<>"nicu") AND
(([RT-FLOW].VENT) Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

I took out the * and the query returned no records at all and there are
records that should show up.


Then I tried this below....
WHERE ((([RT-FLOW].[RM#]) Not Like "ms*" "nicu""picu") AND (([RT-FLOW].VENT)
Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

I get a syntax error something about a missing operand?

Thanks,
Linda

 

[LMB]

Eh? If the value is LIKE "cv*" then a) you don't need the second
asterisk, * means match any length string; and b) you don't need the
other criteria, since "ms", "nicu" and "picu" are all UNlike "cv*".

If this doesn't make sense, please open the query in SQL view. "Not"
logic can be confusing, and it may be easier to parse the query in
SQL; parenthesis nesting is important and hard to visualize in the
grid.

John W. Vinson[MVP]

SELECT DISTINCTROW [RT-FLOW].[RM#], [RT-FLOW].[BD#], [RT-FLOW].[ADM
NUMBER], [RT-FLOW].[LAST NAME], [RT-FLOW].[FIRST NAME], [RT-FLOW].THERAPY,
[RT-FLOW].FREQUENCY, [RT-FLOW].MEDS, [RT-FLOW].VENT, [RT-FLOW].[ORDER
DATE], [RT-FLOW].OrderTime, [RT-FLOW].[RM#], [RT-FLOW].ysnTDP,
[RT-FLOW].ysnOrderRenewal
FROM [RT-FLOW]

WHERE ((([RT-FLOW].[RM#]) Not Like "ms**" And ([RT-FLOW].[RM#])<>"nicu")
AND (([RT-FLOW].VENT) Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter
Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

This works, it filters out all the msicu and nicu patients but I can't add
other areas to exclude.

I tried this below...
WHERE ((([RT-FLOW].[RM#]) Not Like "ms*" And ([RT-FLOW].[RM#])<>"nicu")
AND (([RT-FLOW].VENT) Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter
Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

I took out the * and the query returned no records at all and there are
records that should show up.


Then I tried this below....
WHERE ((([RT-FLOW].[RM#]) Not Like "ms*" "nicu""picu") AND
(([RT-FLOW].VENT) Is Null) AND (([RT-FLOW].[ORDER DATE])=[Enter Date]))
ORDER BY [RT-FLOW].[RM#], [RT-FLOW].[BD#];

I get a syntax error something about a missing operand?

Thanks,
Linda

I lied. The expression with only one* does work..I must have typed in a
date that had no records.

Sorry

 

[John Vinson]

I lied. The expression with only one* does work..I must have typed in a
date that had no records.

so... is it working? or do you still have a problem?

John W. Vinson[MVP]

 

[LMB]

so... is it working? or do you still have a problem?

John W. Vinson[MVP]

No, when I add more than 2 items in the criteria, it doesn't work.