Criteria not working?Please help!

[tessapitchford]

Hi all.

I am trying to effectively filter patients off a hospital data base depending on whether on not they meet various illness criteria.

I have successfully entered the criteria for text entries into my access table, for example, however, I cannot get the query to filter for numbers. I am trying to enter criteria for ranges as shown on various "help" pages, but they're not working and I don't knowk what I'm doing wrong.

for example, a patient can score 1, 2, 3, 4, 5, 6, 7, 8, 9 or 10 on a certain scale, but i only want those patients scoring 7 or less. So in the first row of criteria I entered "<=7" But it just doesn't filter out the numbers.

Am I doing something wrong? I can't understand, because my word criteria are filtering successfully.

anyhelp or further questions would be hugely appreciated!

Tessa

 

[pbaldy]

You don't have the quotes in the criteria, do you?

 

[tessapitchford]

hi
I'm not including quotes in the in the numerical criteria. but when I wrote the text criteria, it automatically put quotes if I left them out.

Although now I see that its only filtering some of my text criteria successfully and not others! and yet they're all entered in the same way.

Another question, aer filters in access case sensitive? cos I know they aren'y in excel.

thanks!

 

[pbaldy]

What is the data type of the field being filtered? A criteria of

<=7

on a numeric field should work fine.

 

[tessapitchford]

Yeah, it is a number field. I've used drop down lists with specified numerical values in the field in the input form I made. I can't understand it. Especially now that some of my text criteria aren't working. would it help to post a copy of what I've done up here do you think?

 

[boblarson]

Yeah, it is a number field. I've used drop down lists with specified numerical values in the field in the input form I made. I can't understand it. Especially now that some of my text criteria aren't working. would it help to post a copy of what I've done up here do you think?

Sorry to jump in here (sorry Paul) but I just saw what you wrote and it made me question:

Do you have any LOOKUP Fields at TABLE level? From your wording here it sounds like it and that can play havoc with what you THINK should be in the criteria.

See here for more:
http://www.mvps.org/access/lookupfields.htm

 

[tessapitchford]

Hi!

No, I don't have any lookup fields. I have an 'autotext' one which is my primary key and then just text and number fields. The number ones either intergers or decimals.

Thanks for all the help! I've kind of been thrown in the deep end here! thought I was figuring it out well but can't get this query thing right!

would it help to post the document or a screen shot of the query or something?

 

[pbaldy]

It would help to post the db, yes.

 

[tessapitchford]

Hi there. I've tried to post the db, but it crashes everytime I've tried, despite resizing etc.. I've been fiddling around a lot and I think I've figured out whats wrong...
Fields are in boldand criteria are in italics.

I have two fields (of about 18) "Competency" and Legal decision maker" which are sort of linked in my work. I want to filter patients who meet certain combinations of criteria in each field. So a patient who is marked "Yes" under "competency" will be accepted if they're marked "No" OR "Not necessary" under "Legal decision maker". But there are 5 possible combinations of these data sets that I am willing to accept, Yes and yes, Yes and not necessary, No and yes, etc...

Combined with this I have other simple criteria adjacent to these columns. For example, my field CNS must also be marked "No". and then there other "OR" criteria with just two options. For example, cause of TBI, they must either be marked "fall" or "MVA". So the logic is something like this...

["Expressive and receptive"language_abilities AND (("yes"competencyAND"yes"legal_decision_maker)OR("No"competencyAND"yes"legaldecisionmaker)ORetc...) AND "No"orthopedic_injuries AND etc.. AND ("Fall"cause_of_TBI OR "MVA"cause_of_TBI) AND.... etc

the criteria for each field work when used in isolation, so I've definitely typed them correctly and i've tested that, it seems to be the combination that is problemmatic and I'm afraid my programming abilities are limited here.further along the table I also have a ranged criteria for a set of numbers GCS must be "=1" OR "<1" (typed on top of eachother) which works on its own but I fear may be interfering too?

I hope that makes sense... any help would be much appreciated! thanks!!!

Tess

 

[jardiamj]

Hello Tessapitchford! (which a Nick Name hu!)
I kind of understand what you are trying to do! but it's a bit hard for me to figure it out without a full picture of your data and your criteria. If you really can't post you db, could you at least post how the data looks like and the SQL statement of your Query.
On that way we may be able to help you.
Good luck. Cheers!