Solved

Date filter

Posted on 2014-12-05
19
193 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

19 Experts available now in Live!

Get 1:1 Help Now