?
Solved

Date filter

Posted on 2014-12-05
19
Medium Priority
?
201 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
Independent Software Vendors: 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!

 
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 668 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 668 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 664 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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