Solved

Date filter

Posted on 2014-12-05
19
196 Views
Last Modified: 2014-12-05
Experts, how would I modify the below so instead of entering the number of days, I can enter a date such as 3/31/15

thank you
Between Date() And Date()+Nz([Enter Number of Days in Future],100000) Or Is Null
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40483250
Between Date() And DateAdd("d",Nz([Enter Number of Days in Future],0), Date())
0
 

Author Comment

by:pdvsa
ID: 40483264
Is that using either days or a date?
0
 

Author Comment

by:pdvsa
ID: 40483273
seems like entereing a date as 3/31/2015 gives me a message of "too complicated to evaluated."

any other idea?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40483277
Not exactly sure what you are after, ...but try this works as a *simple* test
   
    Between Date() And [EnterDate]
0
 

Author Comment

by:pdvsa
ID: 40483335
yes that works.,  wouldnt I need a null handler too?
0
 

Author Comment

by:pdvsa
ID: 40483336
Between Date() And [EnterDate] Or Is Null

correct?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40483353
Between Date() And Nz([Enter Date], Date())
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40483375
pdvsa,

No, null is already taken care of with NZ.

Mike
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 167 total points
ID: 40483390
if your date field does not contain the time values

Between Date() And Nz([Enter Date], Date())  ' if you do not enter a value, will use the same Date as the value


if your date field contains time

Between Date() And Nz([Enter Date], Date() + 1)
0
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 167 total points
ID: 40483407
pdvsa,

It will be helpful to address experts so we will know whom you are responding to. Did you try:

Between Date() And DateAdd("d",Nz([Enter Number of Days in Future],0), Date())

You will be prompted "Enter Number of Days in Future" here you will enter 1000 for example to see the result after a click on OK.
0
 

Author Comment

by:pdvsa
ID: 40483626
Jeff:  your formula works if I add "Or Is Null"
Between Date() And [EnterDate] Or Is Null
I have 78 records.

Rey:  
It seems that both formulas do not pick up Nulls.

Between Date() And Nz([Enter Date], Date() + 1)
Between Date() And Nz([Enter Date], Date())

I have 48 records on both of the formulas.  I tested both more than once.
the field is formatted as date in the table.  Some are null.  Its an import from excel.  Maybe there is something odd with the data in the column but it looks like the only data in the column is either a date or null.


eghtebas:  I was looking to move away from entering the number of days.  It looks as though your formula doesnt take this into account.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40483649
Rey:  
It seems that both formulas do not pick up Nulls.

Between Date() And Nz([Enter Date], Date() + 1)
Between Date() And Nz([Enter Date], Date())

it will Not.. it is suppose to filter only date values..
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40483678
Between Date() And (Nz([Enter Date], Date()) + 1)

Null being, user enters blank.

Do you want when user enters nothing, to show all?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 40483753
FWIW, ...
If this was my project...
I would create a form to enter this "date"
Then use this date to filter the form.

I would "Validate" for nulls and filter the form like this:

Private Sub Command13_Click()
If Me.txtYourDate = "" Or IsNull(Me.txtYourDate) Then
   MsgBox "You must enter a date."
   Me.txtYourDate.SetFocus
   Exit Sub
End If
    Me.Filter = "[Orderdate] Between " & "#" & Date & "#" & " And " & "#" & Me.txtYourDate & "#"
    'MsgBox Me.Filter
    Me.FilterOn = True
End Sub


This way I would have more control over the output, ...I could also remove the filter easily.

I hate just using a parameter prompt because a user could enter anything they wanted: 8/2014, ...December 14, ...6/T/14 (all invalid dates)

simple sample db attached.

JeffCoachman
Database45.mdb
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40483791
How about:

nz([MyDateField],Date()) Between Date() And (Nz([Enter Date], Date()) + 1)
0
 

Author Comment

by:pdvsa
ID: 40483799
I will award points to Jeff.  Let me know if any objections.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40483803
I would prefer if you split the points between all three of us...


Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40483818
My post was just a suggestion of an alternative approach.
capricorn1 and eghtebas had solutions to your question as you posted it.

So a split seems like the right thing to do here...
;-)

Enjoy your weekend
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40484025
pdvsa, It is ok with me.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question