Solved

Date filter

Posted on 2014-12-05
19
194 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 33

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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