Solved

Date filter

Posted on 2014-12-05
19
192 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
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 33

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
 
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 119

Expert Comment

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

Expert Comment

by:Mike Eghtebas
ID: 40483375
pdvsa,

No, null is already taken care of with NZ.

Mike
0
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 33

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 119

Expert Comment

by:Rey Obrero
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 33

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 33

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 33

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now