Dates

[spqr]

HI all,



I have an orders table with the following fields: OrderID, CustomerID, DateOrdered and a customers table with relevant fields.

Customers may have made a number of orders in the past, so for example customer 1 ordered in 2009 and 2010, customer 2 ordered only in 2009

I want to create a query to find all customers that have ordered just in 2009 and NOT 2010.

I have tried a query as follows but I get customers who have ordered in 2009 as well as 2010:

SELECT OrderTable.[Order ID], OrderTable.[Date Ordered], OrderTable.[Customer ID]
FROM OrderTable INNER JOIN (PlantData INNER JOIN StockOrder ON PlantData.[Plant ID] = StockOrder.[Plant ID]) ON OrderTable.[Order ID] = StockOrder.[Order ID]
WHERE (((Year([Date Ordered]))=[please enter first year]) AND (Not (Year([Date Ordered]))=[please enter second year]));


Thanks,

spqr

[weekend00]

I remember I have answered this question last week.

[weekend00]

https://www.accessforums.net/queries...ates-8277.html

[spqr]

I can't seem to get it working. What's the logic behind your query and I can maybe figure out the sql then.

Thanks

[Rawb]

What's the format for the dates? Some of the smaller formats don't include the year as actual data, so the current year is always assumed.

If you're using one of those formats, there's not really an easy way to get the data you're looking for.

Assuming you're using a format that retains the year, try using the DateDiff() function:

SELECT * FROM MyTable WHERE DateDiff("yyyy", [MyDate], Date())=1

The above Query only returns Records where the year value of [MyTable].[MyDate] and the year value of today's date is a positive 1 (meaning [MyTable].[MyDate] is 1 year BEFORE today's year).

Note that even though you're comparing two Date/Time values, the DateDiff() function returns a Number (hence the lack of pound signs around the 1).