Need help setting query criteria from a form

[xsdaver]

I've been tearing my hair out... no doubt a familiar situation for beginners.

After much hair loss, I have ended up with a form that has three fields on
it that I'm trying to use to set a query criteria for the query that provides
the data for the form. The field I'm trying to filter on is [Date Sold]. It
all works if the criteria is:

Between [Forms]![Item Details]![txtDateSoldFilter1] And [Forms]![Item
Details]![txtDateSoldFilter2]

or

Like "*" or Is Null

but if I use:

IIf([Forms]![Item
Details]![chkFilterDateSold]=Yes,"#7/16/1008#","#7/23/2008#")

I get the error message: "The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numberic expression may contain too
many complicated elements. Try simplifying the expression by assigning parts
of the expression to variable."

What I've been trying to get to now is:

IIf([Forms]![Item Details]![chkFilterDateSold]=Yes,"""" Between " &
[Forms]![Item Details]![txtDateSoldFilter1] & " And " & [Forms]![Item
Details]![txtDateSoldFilter2]","Like " & """*""" & " Or Is Null")

which will hopefully show me records that fall between two dates or
everything. I'll also need to be able to show only those records where [Date
Sold] is null, or not sold.

 

[Douglas J. Steele]

Remove the quotes. All you need for dates is the # delimiter:

IIf([Forms]![Item Details]![chkFilterDateSold]=Yes,#7/16/1008#,#7/23/2008#)

You cannot change the type of comparison through an IIf statement. You'll
need a compound condition along the lines of

(Between [Forms]![Item Details]![txtDateSoldFilter1] And [Forms]![Item
Details]![txtDateSoldFilter2]
AND [Forms]![Item Details]![chkFilterDateSold]=True)

If [Forms]![Item Details]![chkFilterDateSold] isn't checked, you'll get all
the records without needing any criteria.

 

[xsdaver]

Remove the quotes. All you need for dates is the # delimiter:...

Getting rid of the quotes is clearly a step in the right direction. I
pasted your expression into the expression builder on the query and Access
took the AND [Forms]![Item Details]![chkFilterDateSold]=True part and made it
a separate column with [Forms]![Item Details]![chkFilterDateSold] as the
field and TRUE as the Criteria. I added False in the next criteria line down
and the query appears to be working!

However, I don't seem to be able to get the combo box recordset that comes
from the query to update when I change any of the three fields. I can change
them and go to the query design window and it's working, but the filtered
list is not showing up in the combo box drop down list. I've tried
"DoCmd.Requery (Me.cboItemLookup)" which is the combo box that accesses the
query to find the record, as well as "DoCmd.Requery (frmqryItemDetails)"
which is the underlying query that provides the data to the combo box.

I have the requery commands executing on lost focus for the date boxes and
after update for the check box.

If I check the box and set the dates and go to the ... button on the row
source for the combo box and look at the data sheet, it's all good. It just
doesn't seem to want to update from within the form.

 

[xsdaver]

Remove the quotes. All you need for dates is the # delimiter:...

This worked! Hooray! Access did move the AND portion of your command to
its own column with [Forms]![Item Details]![chkFilterDateSold] as the Field
and True as the criteria. I had to add a row with False below it to get it
all to work as desired.

However, I can't get the recordset in the combo box to update from within
the form. I've added DoCmd.Requery (cboItemLookup) to execute after
updating the checkbox and after loosing focus for the two date text boxes.
I've also tried putting the query name in for the requery command but to no
avail. If I go to the query from the Row Source line for the combo box and
look at the datasheet view it is correct and the combo box reflects it after
I go back to the form. It just doesn't refresh from the form.