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
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
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


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


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

Open in new window

SharathConnect With a Mentor Data 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

PortletPaulConnect With a Mentor Commented:
>>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.)
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

IrogSintaConnect With a Mentor Commented:
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)
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.
Southern_GentlemanAuthor Commented:
Ahh, finally worked it out. Made it harder for myself. Thanks KRIS
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
See example using method #2 above
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.