Adding Date Criteria

[JBUDLARWOOD]

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.

[sanniep]

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.

[Tanis]

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

[JBUDLARWOOD]

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!

[JBUDLARWOOD]

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.

[sanniep]

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?

[MMcCarthy]

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!

[JBUDLARWOOD]

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.su pplier_name, dbo_po_hdr.po_n o, dbo_po_hdr.orde r_date
FROM dbo_supplier INNER JOIN dbo_po_hdr ON dbo_supplier.su pplier_id = dbo_po_hdr.supp lier_id
WHERE (((dbo_po_hdr.o rder_date)>=Dat e()-1));

[MMcCarthy]

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.su pplier_name, dbo_po_hdr.po_n o, dbo_po_hdr.orde r_date
FROM dbo_supplier INNER JOIN dbo_po_hdr ON dbo_supplier.su pplier_id = dbo_po_hdr.supp lier_id
WHERE (((dbo_po_hdr.o rder_date)>=Dat e()-1));

[JBUDLARWOOD]

It is suppose to be linked tables thru ODBC. Have I missed a step?

[MMcCarthy]

If your tables are linked by odbc they should be up to date. Open one of the tables and spot check the data.

[JBUDLARWOOD]

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.

[JBUDLARWOOD]

Well, today it works. Lets wait and see what tomorrow brings.
Thanks to everyone.

[JBUDLARWOOD]

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

Thanks to everyone!