Query criteria based on dates added on form


I have an access query based on dates added on form

When the start date filter is null, I want to select all dates.

The "*"  doesn't work
.
like (iif(([Forms]![frm_Prime]![filtStartDate])="";"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))

Thanks
Karl001Asked:
Who is Participating?
 
dqmqCommented:
I rather like this, which accepts either date as null.  If startdate is null it pulls everything before end date; if enddate is null, it pulls everything after startdate.  If both dates are null, it pulls everything



Where
[dateSchedul]>=nz([Forms]![frm_Prime]![filtStartDate],[dateSchedul]) and
[dateSchedul]<=nz([Forms]![frm_Prime]![filtEndDate],[dateSchedul])
0
 
peter57rCommented:
Where ([dateSchedul]>=[Forms]![frm_Primes]![filtStartDate] and [dateSchedul]<=[Forms]![frm_Prime]![filtEndDate]) or [Forms]![frm_Primes]![filtStartDate] is null

0
 
Rey Obrero (Capricorn1)Commented:


try this

where iif([Forms]![frm_Prime]![filtStartDate]= "",[dateSchedul], ([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate])

or

where iif([Forms]![frm_Prime]![filtStartDate] & "" = "",[dateSchedul], ([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate])
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HainKurtSr. System AnalystCommented:
or try this:

(nz([Forms]![frm_Prime]![filtStartDate])="" or [dateSchedul]>[Forms]![frm_Prime]![filtStartDate])
 and
(nz([Forms]![frm_Prime]![filtEndDate])="" or [dateSchedul]<=[dateSchedul])

or just this

([Forms]![frm_Prime]![filtStartDate]="" or [dateSchedul]>[Forms]![frm_Prime]![filtStartDate])
 and
(Forms]![frm_Prime]![filtEndDate]="" or [dateSchedul]<=[dateSchedul])

0
 
Eric ShermanAccountant/DeveloperCommented:
Also, I recommend adding a few lines of VBA code before the Query runs that will validate  the start and end date controls on your form.  

If IsNull(Me.filtStartDate) Or IsNull(Me.filtEndDate) Then
    MsgBox "Start Date and End Date are both required fields."
    Exit Sub
End If

DoCmd.OpenQuery ......your query here

 
ET
0
 
GRayLCommented:
This:

like (iif(([Forms]![frm_Prime]![filtStartDate])="";"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))

should be this:

like (iif(([Forms]![frm_Prime]![filtStartDate])=Null;"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))
0
 
Karl001Author Commented:
I tested the 3 first solution.
sol 1 is for "null" date
sol 2, I tried and I was not able to have result.
sol 3, works wells

Thanks everyone
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.