Another Date Query

[linskill]

I am using a query to find records that fall on the following month using the month and year functions.

Month(Now())+1
Year(Now())

This works fine until we get to December and it all falls down.
I know that is due to the year now function but I cant seem to find a way to update it other than do it manually.
Can anyone please point me in the right direction on how to resolve this issue.

 

[pr2-eugin]

So all you need is to find the difference of the month; from the date you enter and today's date should be 1? So for example

[COLOR=Green]' The following would return 1[/COLOR]
? DateDiff("m", Date(), #01/01/2013#)

Also on a side note it is best to use Date() rather than Now() if you are planning on using only Dates and not time..

 

[linskill]

Thanks for the reply, unfortunately I haven't got a clue how I would incorporate that into my query, Could you please elaborate further.

This is the SQL of my query if it helps:

SELECT CustomerT.cusID, CustomerT.Prefix, CustomerT.FirstName, CustomerT.Initial, CustomerT.Country, CustomerT.Surname, OrderDetailQ.*, Month([subsExp]) AS expmonth, Year([SubsExp]) AS expyear, CustomerT.Organization, CustomerT.Address, CustomerT.Address1, CustomerT.Town, CustomerT.County, CustomerT.PostCode, CustomerT.City
FROM CustomerT INNER JOIN (OrderDetailQ INNER JOIN OrderT ON OrderDetailQ.OrderID = OrderT.OrderID) ON CustomerT.cusID = OrderT.cusID
WHERE (((Month([subsExp]))=Month(Now())+1) AND ((Year([SubsExp]))=Year(Now())))
ORDER BY CustomerT.Country DESC , CustomerT.Surname;

 

[pr2-eugin]

It goes down in the WHERE Clause

SELECT CustomerT.cusID, CustomerT.Prefix, CustomerT.FirstName, CustomerT.Initial, CustomerT.Country, CustomerT.Surname, OrderDetailQ.*, Month([subsExp]) AS expmonth, Year([SubsExp]) AS expyear, CustomerT.Organization, CustomerT.Address, CustomerT.Address1, CustomerT.Town, CustomerT.County, CustomerT.PostCode, CustomerT.City
FROM CustomerT INNER JOIN (OrderDetailQ INNER JOIN OrderT ON OrderDetailQ.OrderID = OrderT.OrderID) ON CustomerT.cusID = OrderT.cusID
WHERE ([B](DateDiff("m",Date(),[subsExp]) = 1)[/B])
ORDER BY CustomerT.Country DESC , CustomerT.Surname;

Try it and post back if you get the result you are after..

 

[linskill]

Many thanks, it worked great. It made a lot more sense when I saw it in place.
You know, thanks to this forum I am in danger of learning something

 

[pr2-eugin]

You are most welcome.. Glad to help..

You know, thanks to this forum I am in danger of learning something

We all are..