Complex Criteria

[Kapelluschsa]

Hey everyone

I am having trouble setting the criteria in one of my quaries. What I want the program to do is seperate products into two seperate queries depending on their release date.



For the queries I have the criterias for the release date field set to >Date() and the other to <Date().

This works great, but if the release date is unknown for a product the user leaves that input box blank. This results in the item not showing up in the not yet released querie.

I used the code saying that if the release date = "" then the value is equal to "Unknown".

In the first querie I now have >Date() in the criteria box and = "Unknown" in the Or box below criteria.

When I run the program it says that this querie is either too complex or has an error in the coding.

Is there any way around this?

Thanks,
Sam

[June7]

Date fields must have date or Is Null as criteria. If you only want to match when there is a date and user does not supply value, code will have to provide an alternate. Perhaps some extreme date such as #1/1/1900# so that all records will be included. Use an IIf or Nz function, like:
>Nz(input,#1/1/1900#)

[ssanfu]

In the first querie I now have >Date() in the criteria box and = "Unknown" in the Or box below criteria.

Unless the field has the word "Unknown", it won't return the product. The value is Null. So try:

>Date() in the criteria box and
IsNull() in the Or box below criteria.

[orange]

I think you need something like this in your query

ReleaseType: iif( IsNull(ReleaseDate),"unknown",iif(ReleaseDate<=Dat e(),"Old","Future")

[Kapelluschsa]

If at all possible I would like to keep the Estimated Release Date field as either the correct release date or empty just to avoid confusion. I tried to put IsNull() into the Or slot but it returned an error stating that the function entered contains the wrong number of arguments. If I type "IsNull" I get the same error stated above saying that the expression is either too complex or contains an error.

Any other ideas?

[orange]

If you're using the query wizard you have to have 2 words Is Null

IsNull() is a vba function.

[Kapelluschsa]

That seems like it did the trick.

Thanks guys.

[ssanfu]

If you're using the query wizard you have to have 2 words Is Null

IsNull() is a vba function.

Thanks, Orange. Too much VBA programming..... not enough SQL
Sorry.