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?
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"));
精彩评论