Query Criteria help

[Adam]

Hi All,

I have a query which I want to run off of certain criteria entered into
a form.

I want to be able to have an if sort of criteria, so if nothing is
entered in the "Source" field on the form then show all records however
if something is written in the "Source" field then search for that
item.

The criteria i made is:

IIf ( Forms![Open Contact frm]![Source] = IsNull ,*, Forms![Open
Contact frm]![Source)

It is wrong, but can someone spot what I need to change please?

Adam

 

[Rick B]

I think you can simply put...

Like Forms![Open Contact frm]![Source] & "*"

 

[Lynn Trapp]

Change your criteria to this:

Like "*" & Forms![Open Contact frm]![Source] & "*"

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Adam]

How would this then work with a date range?

I have the criteria: Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]

BUT.. If a date is not entered I want it to return all the data.

Is this possible?

 

[Lynn Trapp]

([YourDateField] Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]) OR ([YourDateField] Is Not Null)


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[tw]

Shouldn't this be
([YourDateField] Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]) OR ([Forms]![Open Contact
frm]![StartDate] is null)

I may have misunderstood what he wanted but is seems to me that your code
will give all records if the date in the record is not null regardless of
whether or not there is data entered on the form.

([YourDateField] Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]) OR ([YourDateField] Is Not Null)


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

How would this then work with a date range?

I have the criteria: Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]

BUT.. If a date is not entered I want it to return all the data.

Is this possible?

 

[John Vinson]

How would this then work with a date range?

I have the criteria: Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]

BUT.. If a date is not entered I want it to return all the data.

Is this possible?

For date ranges I use a bit more complex, but flexible, expression:

WHERE ([datefield] >= CDate([Forms]![Open Contact frm]![StartDate])
OR [Forms]![Open Contact frm]![StartDate] IS NULL)
And
([datefield] < DateAdd("d", 1, CDate([Forms]![Open Contact
frm]![EndDate]) OR [Forms]![Open Contact frm]![EndDate] IS NULL)

This correctly handles datefields containing a time portion, and users
entering oddball date formats; if the user enters a StartDate but no
end date it returns all records after the startdate, etc.

John W. Vinson[MVP]

 

[Lynn Trapp]

Yes it should.... Thanks for the catch.... I wrote too fast.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

Shouldn't this be
([YourDateField] Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]) OR ([Forms]![Open Contact
frm]![StartDate] is null)

I may have misunderstood what he wanted but is seems to me that your code
will give all records if the date in the record is not null regardless of
whether or not there is data entered on the form.

([YourDateField] Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]) OR ([YourDateField] Is Not Null)


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

How would this then work with a date range?

I have the criteria: Between [Forms]![Open Contact frm]![StartDate] And
[Forms]![Open Contact frm]![EndDate]

BUT.. If a date is not entered I want it to return all the data.

Is this possible?

 

[Adam]

Wicked response, thank you !

 

[Adam]

I'm being stupid, does this go in the criteria section of the Query?

 

[Lynn Trapp]

You will notice that John wrote it out as an SQL WHERE clause. The WHERE
clause of an SQL statement IS the criteria section of the QBE grid.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[John Vinson]

I'm being stupid, does this go in the criteria section of the Query?

Sorry! Just using SQL syntax out of habit.

Try copying the criteria string WITHOUT the word WHERE into a cell on
the Criteria line of the grid; or, open the query in SQL view and copy
it into the existing WHERE clause of your query.

There are enough parentheses and OR's in this query that it's going to
look pretty wierd in the query grid!

John W. Vinson[MVP]