DateAdd won't work

I have a simple table in MS Access and i'm having trouble returning orders that have been entered yesterday from the OrdersDate column.  I tried DateAdd("d",1,Date()) but when I
run the query it returns all the order numbers from my table and the OrderDate field just displays yesterdays date down the entire column. Would my date format have anything to do with this. The OrderDate columns field is "9/24/2013 16:22"
Any other methods? Thanks
Southern_GentlemanAsked:
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.

SharathData EngineerCommented:
Are you trying like this and still not getting data?
select * from your_table where date(OrderDate) = DateAdd('D',-1,DATE())

Open in new window

0
PortletPaulfreelancerCommented:
>>the OrderDate field just displays yesterdays date down the entire column
this indicates you have placed the DateAdd() into the select clause
(it should be used in the WHERE clause for this need)

i.e. It should be used as you see above by Sharath (with minus 1 too :)

>>Would my date format have anything to do with this
NO. Not if that field is a date type.
The visible format of a date is not how that information is stored, and the DateAdd() function reads the stored numeric representation directly to do the math.

(IF information is stored in strings that appear to be dates - THEN the stored format is relevant, but you can't used DateAdd() directly on such a field in this scenario.)
0
IrogSintaCommented:
If this is an Access table then Sharath_123's query needs a slight revision:
Select * from your_table where DateValue(OrderDate) = DateAdd('D',-1,DATE())
Or you could also try this:
Select * from your_table where DateValue(OrderDate) =DATE()-1)
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Southern_GentlemanAuthor Commented:
Thanks guy, It just seems so easy but when I'm doing the DateAdd function it comes up with no records. I'm just going to attach a quick sample to see what i'm missing. I made sure that my field was a date and I was putting it in the Where clause. Bashing my head over something so simple.
DateSample.accdb
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I agree that it sounds like you have a calculated field not a criteria with the DateAdd()

Since the Order Date includes the time then you have to options:

1) You will need to use a date range for the order date comparison

Why:  WHen you use Date it has a time of 00:00:00

  #9/24/2013 16:22# does not =  #9/24/2013 00:00:00#  (Date -1)

So you will use a range from 00:00:00 to 23:59:59 as the time

Where OrderDate between DateAdd("d",-1,DATE()) and DateAdd("d",-1,DATE()) + TimeValue(#23:59:59#)

Open in new window


or

Where OrderDate between (DATE() - 1) and (Date() -1 + TimeValue(#23:59:59#))

Open in new window





2) Strip the time from the Order date before the comparison


Where DateValue(OrderDate) = DateAdd("d",-1,DATE())

Open in new window


or

Where DateValue(OrderDate) = ( DATE()-1)

Open in new window

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
Southern_GentlemanAuthor Commented:
Ahh, finally worked it out. Made it harder for myself. Thanks KRIS
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
See example using method #2 above
DateSample-HiTechCoach.zip
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
Query Syntax

From novice to tech pro — start learning today.