Filtering by date

This is probably something simple, just want a second look.  I have a db with a field "Datetobecompleted".  I am trying to run a simple query that will display results for today and today +3.  I have put todays date and went up to the 14th and still no luck.  The query I am using is below.  I want to add that I use this query on another db and it works just fine.  I do not understand why it isnt working on this.  Any help would be more than greatly appreciated.

Query:
SELECT tbljobstodo.[DateToBeCompleted]
FROM tbljobstodo
WHERE (((tbljobstodo.[DateToBeCompleted]) Between Date() And Date()+3));
LVL 2
russell12Asked:
Who is Participating?
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.

 
lwadwellCommented:
first guess ... check the datatype on the column definition.
0
 
russell12Author Commented:
the datatype is set to date/time.
0
 
lwadwellCommented:
and there definitely rows in the table in that date range?
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.

 
russell12Author Commented:
yes.  I have only 1 row with that date.  I am going to copy and paste the date thats in that field below.

5/14/2012

I am so confused about this cause it should work.  I can no believe that this is creating such an issue!!  Thanks though for quick response.   I am sure its something very simple.
0
 
rajshekherCommented:
Hi,

First thing is that your data type of the field should be Date/Time format.

If it is, then you can apply you query.

SELECT tbljobstodo.[DateToBeCompleted]
FROM tbljobstodo
WHERE tbljobstodo.[DateToBeCompleted] Between  GETDATE() and GETDATE() + 6

Hope it will resolve your issue. ( GetDate() is MS SQL Format )
0
 
lwadwellCommented:
Does +4 work?  It might be a boundary condition.
Is there a time portion in the date?
0
 
russell12Author Commented:
what do u mean by time portion?  And to rajshekher, The getdate throws a function error.  I am using ms access 2003 with sql server backend.
0
 
lwadwellCommented:
The field is datetime ... i.e. it could contain a time component as well, e.g. 5/14/2012 05:00:00
0
 
russell12Author Commented:
Oh and the +4 doesnt work either.  This is making no sense to me, I have a db that uses this same filter function Between Date() And Date()+3 and it works flawlessly.
0
 
russell12Author Commented:
Oh ok, I thought thats what u were meaning just wasnt sure.  No the only thing in that field is 5/12/2012.
0
 
lwadwellCommented:
humour me and try :

SELECT tbljobstodo.[DateToBeCompleted], format(tbljobstodo.[DateToBeCompleted], "dd mmm yyyy")
FROM tbljobstodo

Open in new window


no WHERE clause
0

Experts Exchange Solution brought to you by ConnectWise

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
 
russell12Author Commented:
Date To Be Completed      Expr1
2012-05-14 00:00:00.000      2012-05-14 00:00:00.000

thats what the outcome is.
0
 
russell12Author Commented:
If I put #5/14/2012# in the where clause, it works.  Its just something about Date()
0
 
lwadwellCommented:
Try this to see what is returned from the functions:

SELECT tbljobstodo.[DateToBeCompleted], Date(), Date() + 3
FROM tbljobstodo
0
 
russell12Author Commented:
Ok I found the issue.  It is a problem with storing date/time in sql.  The last query you had me run made me find the issue.  Sql server stores the date/time as 2012-05-14 00:00:00.000.  The Date() shows 8/14/2012.  I thought the issue was i put 05/14/2012 but it wasnt.  So i have to have the db put the information in as 08/14/2012.  It shows on the textbox as 8/14/2012.  Thanks for pointing me in the right direction.  I am going to give you the 500 points for this, but i am going to keep it open until i get it fixed.
0
 
lwadwellCommented:
Wait ... month 5 is May, 8 is August ... I was looking straight past that.
0
 
russell12Author Commented:
Ok finally it works.  I knew it was something simple.  Thank you so much for your help and walking through this with me.  I was working on this simple crap for 4 hrs!!! Thanks again!!

The solution was the way it was storing in the sql db.
0
 
lwadwellCommented:
It is always the obvious that gets you ... me ... everybody.
0
 
als315Commented:
Upload your table with some sample strings. Your syntax seems correct.
DBdate.accdb
0
 
russell12Author Commented:
Thanks for helping me get through this!!
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.