i am trying to write a query that uses the date and year function
(DatePart("yyyy",Date$())+1) & DatePart("m",Date$())
it works until I get to Nov And Dec of the current date (on computer)
example 12-1-2008 (current date) the query should show 1-1-2009 data
have tried adding mod13 to function this will work for Nov, but has to be changed by hand

any help would be appricated

What data does your field you are limiting by use?

I would just use DateAdd to get the date.

Hi -

Or as an option (if what you're after is the 1st day of the following month), try the DateSerial() function:

? date()
12/22/2008

? DateSerial(year(Date()), month(Date()) + 1, 1)
1/1/2009

HTH - Bob

We've got multiple forum postings involved. See his post at
http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/50746/query-will-not-work-between-years.

It goes into a lot more detail.

Bob

the query is looking for data from two fields
Expr2: DatePart("yyyy",[Membership Data]![national membership renewal date])
using (DatePart("yyyy",Date$())+1)
and
Expr1: DatePart("m",[Membership Data]![national membership renewal date])
using DatePart("m",Date$())
the idea is to see when the members National dues and Regional dues are due
the query works until Nov & Dec when the query should report dates in the next year

Hi -

Your use of the Date$() function (rather than the Date() function) indicates that your dates are stored as text rather than in Date/Time data type.

If this is the case, it's not a great idea and you should consider converting your text dates to date/time data format. Failing to do so deprives you of the many Date() related subs and functions and adds an extra (and unnecessary) layer of complexity to your data handling.

If you'll provide a practical example of what you're trying to accomplish, I'll show you how (using dates in date/time data type) to return the response you're after. (Actually, it's already been provided in this thread and the forum I pointed to earlier.)

Best Wishes - Bob

I checked the membership table and all dates are in Date/Time format

all dates in table are in Date/Time format not in text

Chet -

If your dates are in date/type data type, quit using Date$() since it refers to text and use instead Date().

As far as a practical example, please explain what records you are attempting to see, based on what date.

Bob

the access we are using is 2003

looks at date joined
Expr1: DatePart("m",[Membership Data]![Date Joined])
using
(DatePart("m",Date$())+2)
reports the current month plus two months
(if current month is Nov 2008 query would show Jan 2009)
this is used for the printing in our newsletter (45 day lead time)

Expr2: DatePart("yyyy",[Membership Data]![Date Joined])
DatePart("yyyy",Date$())-10

10 year anniversary by year queryExpr1Expr211998119981199811998119981199831998 31998319983199841998419985199851998519985199851998 51998519985199861998619987199871998819988199881998 81998819989199810199810199810199811199811199811199 8121998121998

when query runs in 11 or 12 no results are shown
I can force a result by adding mod 12 or 13 in formula, but have to remove it in Jan.
problem is moving up to next year in the last 2 months

having same proplem with query for region dues billing
Exp1 shows 12 (for Dec) using formula below
DatePart("m",Date$())

exp2 shows 2009 using the formula below
(DatePart("yyyy",Date$())+1)
shows current date

would like it to show one month ahead
(if current month is Dec 2008 would like it to show Jan 2009)

thank you for any help

Chet -

Hold on buddy! Forget all your formulas, they're not the way to go.

Please say something like:

"I'd like to return all records where the member will have 12 years membership as of this month."

- or -

"I'd like to return all records where the member will have 12 years membership as of this month or the next two months."

Once again, spare us your formulas and just tell us in simple English what you're trying to accomplish.

Bob

Gotta agree with Bob on this. What you're doing is assuming that you need to do this in a certain way. So, as Bob has said, tell us in plain english what you are trying to accomplish and we can come up with the formula to make it work.

query 1
I would like to return all members that have 10 years plus two months from the current month,
using the [Membership Data]![Date Joined] field
query 2
I would like to return all members that have national dues due plus one month and the next three years, from the current date
using the [Membership Data]![national membership renewal date]

thanks for your help with this

Hi -

Suggest you lookup the DateAdd() and DateSerial() functions in the Help file.

query 1
I would like to return all members that have 10 years plus two months from the current month, using the [Membership Data]![Date Joined] field

10 years + 2 months = 122 months so, if your desire is to show only those members who joined 122 months from the current date(), place either of the following two in the criteria cell of your [Date Joined] field:

between DateAdd("m", -122, DateSerial(year(date()), month(Date()), 1)) and DateAdd("m", -122, DateSerial(year(Date()), month(Date()) + 1, 0))
-or-
>=DateAdd("m",-122,DateSerial(Year(Date()),Month(Date()),1)) And <=DateAdd("m",-122,DateSerial(Year(Date()),Month(Date())+1,0))

This is going to return the records of those members who joined between 10/1/98 and 10/31/98 (i.e., the first and last day of the month, regardless of the length of the month). Example from the debug (immediate) window:

? DateAdd("m", -122, DateSerial(year(date()), month(Date()), 1))
10/1/1998

? DateAdd("m", -122, DateSerial(year(Date()), month(Date()) + 1, 0))
10/31/1998

--------------------------------------------------------------------

query 2
I would like to return all members that have national dues due plus one month and the next three years, from the current date using the [Membership Data]![national membership renewal date]

I don't understand what you're trying to get here. Given a [national membership renewal date] of 24 Dec 2008, what do you want to return, i.e. Between what date and what date?

Bob

I would like to see the Jan 2009 renewels when date on coomputer is Dec of 2008

Hi -

I would like to see the Jan 2009 renewels when date on coomputer is Dec of 2008

Place this in the criteria cell for [national membership renewal date]. It will return records for the month following the date the query is run:

between DateSerial(year(Date()), month(Date()) + 1, 1) AND DateSerial(year(Date()), month(Date()) + 2, 0)

To break it down further (from the debug (immediate) window) note: today is 12/24/08


' first day of next month
? DateSerial(year(Date()), month(Date()) + 1, 1)
1/1/2009

'last day of next month
? DateSerial(year(Date()), month(Date()) + 2, 0)
1/31/2009

HTH - Bob