Date Expressions in query

[JBUDLARWOOD]

Does anyone have any idea why some of the queries that I have that use a Date expression will all of a sudden stop returning results? I have one right now that I am trying to retrieve just the orders entered yesterday. I use DateDiff("d",[order_date],Now()) and I use 1 in the criteria. It has worked fine until today. If I take the 1 away, I get results.

[cyberdwarf]

Seems to me that your code may fail if the TIME of the order with yesterday's date will be key.

NOW() returns date AND time, so, if NOW() is less than 24 hours later than the order date/time DATEDIFF will return 0.

Try using Date() instead of Now()

HTH

Steve

[JBUDLARWOOD]

No luck Steve. This has been an ongoing problem with my queries for awhile. One day they work, the next day they quit!

[cyberdwarf]

Can you post the order-date data which causes your code to fail?

Steve

[JBUDLARWOOD]

Steve.. Here is the whole SQL query:

SELECT p21_view_ord_ac k_hdr.order_no, p21_view_ord_ac k_hdr.order_dat e, DateDiff("d",[order_date],Date()) AS Expr1, oe_hdr_salesrep .salesrep_id, p21_view_ord_ac k_line.qty_orde red, p21_view_ord_ac k_line.item_id, p21_view_ord_ac k_line.item_des c, p21_view_ord_ac k_line.unit_pri ce, p21_view_ord_ac k_line.extended _price, contacts.last_n ame, p21_view_ord_ac k_hdr.ship2_nam e, p21_view_ord_ac k_hdr.ship2_add 1, p21_view_ord_ac k_hdr.po_number
FROM ((p21_view_ord_ ack_hdr INNER JOIN p21_view_ord_ac k_line ON p21_view_ord_ac k_hdr.order_no = p21_view_ord_ac k_line.order_no ) INNER JOIN oe_hdr_salesrep ON p21_view_ord_ac k_hdr.order_no = oe_hdr_salesrep .order_number) INNER JOIN contacts ON oe_hdr_salesrep .salesrep_id = contacts.id
WHERE (((DateDiff("d" ,[order_date],Date()))=1) AND ((oe_hdr_salesr ep.salesrep_id) ="1008") AND ((oe_hdr_salesr ep.primary_sale srep)="Y"))
ORDER BY p21_view_ord_ac k_hdr.order_dat e DESC , oe_hdr_salesrep .salesrep_id;

[cyberdwarf]

Sanka

How about:-

DateDiff("d",[order_date],Date()))<=1

Steve

[JBUDLARWOOD]

Steve.. as you can see by my status, Im a newbie. Where do I enter that line of text?

[JBUDLARWOOD]

never mind that last reply. When I first read your reply, the info you sent me was scrambled and had extra characters. Anyway, I tried it and it still doesn't work.

[mlcampeau]

You have more than one piece of criteria:
[CODE=sql]WHERE (((DateDiff("d" ,[order_date],Date()))=1) AND ((oe_hdr_salesr ep.salesrep_id) ="1008") AND ((oe_hdr_salesr ep.primary_sale srep)="Y"))[/CODE]
Are you sure that there are results that meet all 3 pieces of criteria? i.e. Are there results where the salesrep id=1008 and the primary_salesre p=Y AND the DateDiff=1? Maybe there are no entries for that sales rep yesterday? Maybe the primary_salesre p is set to N instead of Y? Doublecheck that the query should be returning results in the first place. This could explain why sometimes it works and sometimes it doesn't.

[JBUDLARWOOD]

OK, problem somewhat solved. The guy who created all these reports, who is no longer with us, created these reports importing the tables and then using something called DTS. DTS hasn't worked right since he left and I have absolutely no idea how it works. The only fix I can think of is to recreate the queries and linking the tables instead of importing them. This, unfortunately doesn't always seem to work either.