• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

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
0
Karl001
Asked:
Karl001
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now