Kayleigh
Member
- Local time
- Today, 03:34
- Joined
- Sep 24, 2020
- Messages
- 706
Hi,
I have written a query to find out all dates within last week, using a date commencing field. This worked very well:
I then tried implementing with another similar query but got a date mismatch when trying to add criteria to the date commencing field. (Although this worked fine above.)
Any ideas why this is happening?
I have written a query to find out all dates within last week, using a date commencing field. This worked very well:
SQL:
SELECT tblOrders.fldOrderID
FROM tblOrders INNER JOIN qryAddressTradeRetail ON tblOrders.fldOAddressID = qryAddressTradeRetail.fldAddressID
WHERE (((DateAdd("d",-Weekday([fldOCreated])+1,[fldOCreated]))>Date()-7) AND ((tblOrders.fldOStatusID)=10) AND ((qryAddressTradeRetail.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=1));
SQL:
SELECT tblOrders.fldOrderID, tblOrders.fldOTotalQuote, tblOrders.fldOSoldDate, DateAdd("d",-Weekday([fldOSoldDate])+1,[fldOSoldDate]) AS DateCommencing
FROM tblOrders INNER JOIN qryAddressTradeRetail ON tblOrders.fldOAddressID = qryAddressTradeRetail.fldAddressID
WHERE (((tblOrders.fldOStatusID)=8 Or (tblOrders.fldOStatusID)=11 Or (tblOrders.fldOStatusID)=13 Or (tblOrders.fldOStatusID)=14 Or (tblOrders.fldOStatusID)=15) AND ((tblOrders.fldOSoldDate) Is Not Null) AND ((qryAddressTradeRetail.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=1));
Any ideas why this is happening?