Null criteria in Query

[Edgarr8]

I have a table like this

Ranch, Area, ID, Acres , Trees, ect....
01 001-01, 30 , 900 ,
01 001-02, 15 , 500 ,
02 ,N , 002-01, 20 , 20 ,
02 ,S ,002-02, 15 ,100 .

Thats a small sample of my table, But for some "ranches" i have an area and some I do not,

I would like my query to ask the user to enter the ranch # they want to look at and the area if any.

for my creteria under ranch i entered

like [Enter Ranch #], then creaded an expression with the Is Null

which shows all my option is case none were typed in...

Now i want it to ask for both the ranch and area, and return everything for a ranch if the area is blank.

But how?

I tried doing the "Is Null" for both of them but it does not return anything.

 

[ajetrumpet]

Ed,

I'm sure if you uploaded the database here, you could get it back with the problem fixed. However, the same concept you want to implement is explained here: http://www.access-programmers.co.uk/forums/showthread.php?t=103312

 

[just.a.guy]

I don't know. You may be looking for something like this:

SELECT
..............
FROM.....
WHERE RANCH like [Enter Ranch #]
AND AREA like trim([Enter Area]) + "*"
----------------------------------

This should result in two prompts
a. "Enter Ranch #"
b. "Enter Area".

If you enter an "AREA" value, such as "001-01",
then you will get a pattern of "001-01*". This tells the
SQL engine to give all rows that start with
"001-01".

If you reply "001", then you will get "001*"'
This should give you both rows "001-01" and "001-02".
(assuming ranch no = 01).

If you just press the entery key you get "*" which
says all possible values for AREA.

The "*" is a wild card character which represents
zero or more unspecified characters. There is
a wild card characer for a "single" position, but
I don't remember which character it is.

Note if your user responds "*1", then it will
become "*1*" which says any row where
the AREA column contains a "1" character.

Noter because you are using "like" for the
ranch # your user could respond "0*",
this would give all ranches that have a
leading zero character in their ranch #.

Hope this helps.

 

[Edgarr8]

Thank you both for your responses,

but im still having trouble..

just.a.guy, what you posted gave me a really good idea of what i need to do but my problem wasnt my ID, it was my area being "N,S" north or south.

In some cases we have a ranche with 2 locations N,S, (004 N, and 004 S)and most of the others we dont we just have a Ranch (001, 002) but for my desing i need to have it 004 in one column and the are in another, because we dont always use it.

But becuase one of the reports is connected to a map, i want it to ask the user what ranch and what area..if any...this is what i have


SELECT Ranch, Area,....

FROM .....

WHERE Ranch Like [Enter Ranch # (000) or Press Enter for all:]) AND Area Like [Enter Area:])) OR ((([Enter Ranch # (000) or Press Enter for all:]) Is Null) AND (([Enter Area:]) Is Null))
ORDER BY Ranch

If i select a ranch which has an area i get what i want, but once i select a ranch that doentt have an are then i dont get anything back.

Maybe there is something else I should use istead of "AND"

 

[Edgarr8]

Ok I did not get it how i wanted but i found a solution.

I created a query just with my ranch stuff, where it asked what ranch i wanted..

then based on that I created another query where i included the area.

that way, it would ask me for a ranch, then asked for an area, returning all values if i did not specify one.

Thanks again,
but if you find anything i will still be intrested.