Query Criteria help

  • Thread starter Thread starter Adam
  • Start date Start date
A

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
 
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?
 
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.

Lynn Trapp said:
([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


[email protected] said:
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?
Click to expand...
Click to expand...
 
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?
Click to expand...

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]
 
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


tw said:
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.

Lynn Trapp said:
([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


[email protected] said:
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?
Click to expand...
Click to expand...
Click to expand...
 
I'm being stupid, does this go in the criteria section of the Query?
Click to expand...

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]
 
Back
Top