By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,516 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Adding Date Criteria

JBUDLARWOOD
P: 32
I'm doing a simple query for purchase orders that were entered yesterday. In the criteria box I use Date()-1 and get no results. Why is this not working? If I delete the criteria, it returns all orders.
Oct 10 '06 #1
Share this Question
Share on Google+
13 Replies


P: 48
I'm doing a simple query for purchase orders that were entered yesterday. In the criteria box I use Date()-1 and get no results. Why is this not working? If I delete the criteria, it returns all orders.
Is your datefield formatted as Date/Time in the table? Or try Now()-1 as criteria.
Oct 11 '06 #2

100+
P: 143
Now() won't work, it includes time in Now. Date()-1 will work if the field in the table is of the data type Date/Time
Oct 11 '06 #3

JBUDLARWOOD
P: 32
Thanks for the replies.
Neither seemed to work so I tried >=Date()-1 and that worked. Don't know why, but it did.
Thanks Again!
Oct 11 '06 #4

JBUDLARWOOD
P: 32
Ok. Icame into work this morning and my query won't work now. It acts like it is not going out to pick up the current data. I can get all the POs except the ones that I want which are yesterdays.
Oct 12 '06 #5

P: 48
Ok. Icame into work this morning and my query won't work now. It acts like it is not going out to pick up the current data. I can get all the POs except the ones that I want which are yesterdays.
On a correctly formatted date/time field your expression should definitely work.. It even works on text when it's correctly formatted.. Do you use other criteria or are you filtering the wrong field?
Oct 16 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Open the query in design view then go to SQL view and copy and paste the query here.

Also confirm the data type of your date field and its format.

Thanks for the replies.
Neither seemed to work so I tried >=Date()-1 and that worked. Don't know why, but it did.
Thanks Again!
Oct 16 '06 #7

JBUDLARWOOD
P: 32
The problem seems like the query is not going out and getting the current data. It only seems to work from the date I set up the query and back, not whats current.

SELECT dbo_supplier.supplier_name, dbo_po_hdr.po_no, dbo_po_hdr.order_date
FROM dbo_supplier INNER JOIN dbo_po_hdr ON dbo_supplier.supplier_id = dbo_po_hdr.supplier_id
WHERE (((dbo_po_hdr.order_date)>=Date()-1));
Oct 17 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Is this a pass thru sql query or is it set up to use tables linked by odbc. If it is only returning data from when set up did you import the tables?



The problem seems like the query is not going out and getting the current data. It only seems to work from the date I set up the query and back, not whats current.

SELECT dbo_supplier.supplier_name, dbo_po_hdr.po_no, dbo_po_hdr.order_date
FROM dbo_supplier INNER JOIN dbo_po_hdr ON dbo_supplier.supplier_id = dbo_po_hdr.supplier_id
WHERE (((dbo_po_hdr.order_date)>=Date()-1));
Oct 17 '06 #9

JBUDLARWOOD
P: 32
It is suppose to be linked tables thru ODBC. Have I missed a step?
Oct 17 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
If your tables are linked by odbc they should be up to date. Open one of the tables and spot check the data.
Oct 17 '06 #11

JBUDLARWOOD
P: 32
OK, this is starting to make sense. When I created the query, I imported the tables instead of linking them. Let me try it by linking and see if that will work.
Oct 17 '06 #12

JBUDLARWOOD
P: 32
Well, today it works. Lets wait and see what tomorrow brings.
Thanks to everyone.
Oct 17 '06 #13

JBUDLARWOOD
P: 32
Ok folks, thats where I went wrong. I was Importing instead of Linking the tables.
Live & Learn.

Thanks to everyone!
Oct 18 '06 #14

Post your reply

Sign in to post your reply or Sign up for a free account.