date is less than another date in 2 fields

Access 2010 :
  Need a query

I have a table:
tblData  - table

company =    text
disp_start       =   date/time
disp_end        =   date/time

I need a query that gives me a result showing all the records  that have a date less then  "disp_end"
and then also show me how many days less than the  "disp_end"  date.

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
...or this to see yes or no, literally:
SELECT YourTable.Company, YourTable.dispStart, YourTable.dispEnd, DateDiff("d",[dispStart],[dispEnd]) AS Diff, IIf([dispEnd]<[dispStart],-1,0) AS EndBeforeStart, IIf([dispEnd]<[dispStart],0,-1) AS StartBeforeEnd, IIf([dispEnd]<[dispStart],"Yes","No") AS EndBeforeStartYesNo
FROM YourTable;

I am sure you can adapt to fit your needs...
;-)

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
that have a date less then  "disp_end"
What is the name of this other date field?
0
 
FordraidersAuthor Commented:
field:
disp_start       =   date/time  

field:
 disp_end        =   date/time
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
Still not clear...
The SQL synatx would be something like this:
WHERE SomeDate<disp-end

In this case, ...what would "SomeDate" be?

In other words, what date is to be less than disp-end?

...or am I misunderstanding something?
0
 
Jeffrey CoachmanMIS LiasonCommented:
For example (based of your design), you would have a dataset like this:

Company      dispStart      dispEnd
Company1      1/1/2015      1/7/2015
Company2      1/2/2015      1/12/2015
Company3      1/3/2015      1/11/2015
Company4      1/4/2015      1/10/2015
Company5      1/5/2015      1/15/2015
Company6      1/6/2015      1/9/2015
Company7      1/7/2015      1/13/2015
Company8      1/8/2015      1/14/2015
Company9      1/9/2015      1/18/2015
Company10      1/10/2015      1/20/2015
Company11      1/11/2015      1/12/2015
Company12      1/12/2015      1/22/2015
Company13      1/13/2015      1/18/2015
Company14      1/14/2015      1/21/2015
Company15      1/15/2015      1/16/2015
Company16      1/16/2015      1/17/2015
Company17      1/17/2015      1/19/2015
Company18      1/18/2015      1/25/2015
Company19      1/19/2015      1/27/2015
Company20      1/20/2015      1/21/2015
Company21      1/21/2015      1/29/2015
Company22      1/22/2015      1/26/2015
Company23      1/23/2015      2/1/2015
Company24      1/24/2015      1/26/2015
Company25      1/25/2015      1/27/2015
Company26      1/26/2015      2/4/2015
Company27      1/27/2015      2/5/2015
Company28      1/28/2015      2/1/2015
Company29      1/29/2015      2/6/2015
Company30      1/30/2015      2/9/2015
Company31      1/31/2015      2/8/2015
Company32      2/1/2015      2/6/2015

...What would be the results you expect from the query, ...based on this data?

Again, I may be missing something, but I am a bit confused...

Jeff
0
 
FordraidersAuthor Commented:
=datediff('d', [disp_end],[disp_start]

??

but I would like a "Boolean" say its less then the date  instead of  'd'
0
 
Jeffrey CoachmanMIS LiasonCommented:
...that gives me this:
Company      dispStart      dispEnd      Diff
Company1      1/1/2015      1/10/2015      9
Company2      1/2/2015      1/12/2015      10
Company3      1/3/2015      1/12/2015      9
Company4      1/4/2015      1/12/2015      8
Company5      1/5/2015      1/6/2015      1
Company6      1/6/2015      1/14/2015      8
Company7      1/7/2015      1/15/2015      8
Company8      1/8/2015      1/9/2015      1
Company9      1/9/2015      1/14/2015      5
Company10      1/10/2015      1/20/2015      10
Company11      1/11/2015      1/19/2015      8
Company12      1/12/2015      1/15/2015      3
Company13      1/13/2015      1/22/2015      9
Company14      1/14/2015      1/19/2015      5
Company15      1/15/2015      1/16/2015      1
Company16      1/16/2015      1/21/2015      5
Company17      1/17/2015      1/21/2015      4
Company18      1/18/2015      1/22/2015      4
Company19      1/19/2015      1/27/2015      8
Company20      1/20/2015      1/25/2015      5
Company21      1/21/2015      1/22/2015      1
Company22      1/22/2015      1/24/2015      2
Company23      1/23/2015      2/2/2015      10
Company24      1/24/2015      1/31/2015      7
Company25      1/25/2015      2/1/2015      7
Company26      1/26/2015      1/27/2015      1
Company27      1/27/2015      2/4/2015      8
Company28      1/28/2015      2/1/2015      4
Company29      1/29/2015      2/1/2015      3
Company30      1/30/2015      2/2/2015      3
Company31      1/31/2015      2/1/2015      1
Company32      2/1/2015      2/7/2015      6

Still confused as to what you want the final output to be...?

Please post the exact results you need from this dataset, ...or at least the first few records, ...just so I can understand what you need here...
0
 
FordraidersAuthor Commented:
if the "dispStart" is ever greater then the "dispend"  I wuld get a "yes" value..

but the results are correct.
0
 
Jeffrey CoachmanMIS LiasonCommented:
...Or do you have "Random" dates, ...and some end dates and actually before the start date...

In which case your question would be :
"Show me with a Boolean, which End dates are before the start date..."

Then you would have something like the attached sample,
I included both sides of the equation, ...so you can pick which one you need...
...

Jeff
Database69.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
In your form or report, ...you would elect to use a checkbox control to display the appropriate status.
0
 
Jeffrey CoachmanMIS LiasonCommented:
did any of the above work for you?
0
 
FordraidersAuthor Commented:
Thanks very much !!
0
 
Jeffrey CoachmanMIS LiasonCommented:
yes, ...sorry for the confusion...
;-)

enjoy your weekend.
;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.