Access Query Criteria "Not" not working?

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
161
Hello, and thank you in advance if you can help.
I have a query where I created a column "Outside_Safe_Dates" where if an observation date is outside a safe date range it puts "Outside". That is working fine, but now I want to filter out those that are "Outside", so in that column, under criteria, I put Not "Outside" and it returned an empty data set. When I went back to the design, it had changed it to <> "Outside". I tried other variations like using parentheses, and it always returns the blank data. I then thought perhaps I couldn't run it within the same query that the column was created in, so I made a query of the query and the same thing happens. Does anyone have an idea on why this may be happening? I think it could be that it isn't a physical table that is the base of my query, but I am new to access, so I am not sure. I have a large dataset, so I want to minimize the number of physical tables I need to load. To do much more, I would have to create another database to develop it much further, and I would like to avoid that if I can.
Thank you for your time,
Maggie
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,808
If everything in Outside is pulled,there won't be any inside. You cannot filter.
just run another query for Inside.
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
161
ranman256,
Thank you for your reply, but I am not sure I understand what you mean. I created a column with:
Outside_safe_Dates: IIf([DAY-MONTH_VALUE]<[START_Date_VALUE],"Outside",IIf([DAY-MONTH_VALUE]>[END_Date_VALUE],"Outside"))

This gives me a column with blanks for what would be "Inside" and "Outside" for records that fall outside the safe date range. Are you saying that I should modify the expression to list "Inside" Or "outside, thus no blanks? If so, do you know how I would modify the expression, or am I getting what you are saying all wrong?
Thanks,
Maggie
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
161
ranman256,
I got it! Sorry not to have caught on before my response. I modified the expression to: Outside_safe_Dates2: IIf([DAY-MONTH_VALUE]<[START_Date_VALUE],"Outside",IIf([DAY-MONTH_VALUE]>[END_Date_VALUE],"Outside","Inside"))

I then ran the criteria Not "Outside" and it works. Funny that when I go back into design view it has changed it to <>"Outside" vs the Not that I put, but it is working.
Thanks for your help!
Best Wishes,
Maggie
 

Forum statistics

Threads
1,084,809
Messages
5,380,034
Members
401,640
Latest member
LW1993

Some videos you may like

This Week's Hot Topics

Top