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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.