IIf function inside Access query

Question

I have a query and I would like to use an IIf function as part of the criteria. Here is the full SQL:

SELECT Hits.HitID, Hits.ListingID, Hits.HitCount, Hits.HitDate, Hits.HitTypeID, Hits.IsDeleted
FROM Hits
WHERE (((Hits.HitDate)>=[Forms]![frmReports]![txtStartDate]) AND ((Hits.IsDeleted)="N"));

Here is the piece of code that causing me anguish:

>=[Forms]![frmReports]![txtStartDate]

If I have a date on frmReports, this will work fine; however, if no date is entered, this returns 0 records (I want it to return ALL records, if this is the case).

How can I make this work?

Answer 1

Try this:

SELECT Hits.HitID, Hits.ListingID, Hits.HitCount, Hits.HitDate, Hits.HitTypeID, Hits.IsDeleted
FROM Hits
WHERE (((Hits.HitDate)>=nz([Forms]![frmReports]![txtStartDate],"1/1/1")) AND ((Hits.IsDeleted)="N"));

or this

SELECT Hits.HitID, Hits.ListingID, Hits.HitCount, Hits.HitDate, Hits.HitTypeID, Hits.IsDeleted
FROM Hits
WHERE (((Hits.HitDate)>=[Forms]![frmReports]![txtStartDate]) AND ((Hits.IsDeleted)="N"))
    OR (([Forms]![frmReports]![txtStartDate] = "") AND (Hits.IsDeleted="N"));

Answer 2

if no date is entered, this returns 0 records (I want it to return ALL records, if this is the case)

SELECT
    h.HitID,
    h.ListingID,
    h.HitCount,
    h.HitDate,
    h.HitTypeID,
    h.IsDeleted
FROM
    Hits AS h
WHERE
    (h.HitDate>=[Forms]![frmReports]![txtStartDate]
        AND h.IsDeleted="N")
    Or [Forms]![frmReports]![txtStartDate] Is Null;

If you meant all records where IsDeleted="N" when no value entered for txtStartDate, change the WHERE clause to this:

WHERE
    h.IsDeleted="N"
    And (h.HitDate>=[Forms]![frmReports]![txtStartDate]
    Or [Forms]![frmReports]![txtStartDate] Is Null);