Filtering by dates & numbers

[marcuscoker]

Hi was wondering if anybody could help me with this. I have a report that is based on a query. The query filters the records by textboxes on a form. At the moment I am filtering one of the date fields like so

>=[Forms]![ReportsMenu]![txtstartdate] And <=[Forms]![ReportsMenu]![txtenddate]

But what I also want to do is have it so that the user can also leave the date fields blank and still get all the records, like this

Like [Forms]![ReportsMenu]![frmClinic] & "*" Or Is Null

But the above does not seem to work when using the >= <= can anybody help me with this, I would really appreciate it.

Thanks

Marcus

 

[ShaneMan]

Hey Marcus,

I hope I'm not reading your question wrong but it seems that all your asking to do is check and see if your date fields have a date in them. If they do then filter report according to the dates. If they don't then don't filter the report at all. If that's right then something like the following should work.

If Trim(Nz([txtStartDate],"")) = "" And Trim(Nz([txtEndDate],"")) = "" Then
'Don't filter the report
Else
'Filter the report
End If

Hope something like this is what you where looking for,
Shane

 

[marcuscoker]

Hi Shane

Thanks for that

Yes, that is what I have been trying to do. Can I use an if else statement in a query criteria?

Thanks

Marcus

 

[marcuscoker]

Hi

I have had a quick try, here is the SQL code
SELECT WaitingCrossTab.RegID, WaitingCrossTab.APPTDate, WaitingCrossTab.DeferredApptDate, WaitingCrossTab.GPLetterDate, WaitingCrossTab.DateAppointment, WaitingCrossTab.WaitingTimes, WaitingCrossTab.Outcome, WaitingCrossTab.Clinic, WaitingCrossTab.clindesc, WaitingCrossTab.TimeWaitedWeeks, 1 AS Counting, Round([timewaitedweeks],0) AS WaitingTimesGrouping
FROM WaitingCrossTab
WHERE (((WaitingCrossTab.GPLetterDate)=IIf([Forms]![ReportsMenu]![txtstartdate]="","*",(WaitingCrossTab.GPLetterDate)>=[Forms]![ReportsMenu]![txtstartdate] And (WaitingCrossTab.GPLetterDate)<=[Forms]![ReportsMenu]![txtenddate])) AND ((WaitingCrossTab.Clinic) Like [Forms]![ReportsMenu]![frmClinic] & "*" Or (WaitingCrossTab.Clinic) Is Null) AND ((WaitingCrossTab.TimeWaitedWeeks)>=[Forms]![ReportsMenu]![txtminwait] And (WaitingCrossTab.TimeWaitedWeeks)<=[Forms]![ReportsMenu]![txtmaxwait]))
ORDER BY WaitingCrossTab.TimeWaitedWeeks;

Didn't seem to do it,

Any ideas where I am going wrong would be really appreciated

Thanks

Marcus