Last Year/Next Year Calculation

[Ben]

I currently have a Query that converts [Meeting Week], [Meeting Day] and
[Meeting Month] from my Table into the proper date for the current year in
[Meeting Date] in my Query.

Meaning, I can have [1] (first), [2] (Monday) and [1] (in January) in my
Table and [Meeting Date] in my Query will tell me that it is "January 5,
2004."

However, I need to be able to modify my code to either make the appropriate
calculations one year previous or one year in the future depending on the
instance. While I know there are other ways to accomplish this conversion, I
am married to this method so I really need a way to modify this code to find
the given date for a given day of the week in a given month one year in the
future and one year in the past.

My current code reads as follows:

SELECT Format(DateSerial(Year(Date()),[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

What needs to be added, and where?

Thank you in advance.

 

[Ken Snell [MVP]]

Hello Ben...

The code that you posted looks like some code that I'd posted to help Mario
and Ben (same person? same as you?) in early July 2004. So let me try to
expand this query's capabilities with you.

Let's try something and determine if it's what you need (I don't have your
table/data set up for testing here, so we'll trial and error as we go). I've
added a parameter into the query that will prompt you to enter a number (+1
for one year forward, -1 for one year backward).

SELECT Format(DateSerial(Year(Date())+[Enter the number of year(s) to go
forward (positive) or to go backward (negative):],[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

We may also need to modify the other DateSerial function within the query,
but let's just do this one first and see if it works.

 

[Ben]

Thank you so much. First, thank you for helping me originally (I'm the same
Ben). Second, thank you for helping me understand where the "+/-1" needed
to go and how to format it. I played around with different formats and with
different placements, but never got it to work. I made the change where you
suggested and that fixes the year. The further modification to the
DateSerial is required - I did it and everything is working like a dream. I
am in your debt. Cheers.

Hello Ben...

The code that you posted looks like some code that I'd posted to help Mario
and Ben (same person? same as you?) in early July 2004. So let me try to
expand this query's capabilities with you.

Let's try something and determine if it's what you need (I don't have your
table/data set up for testing here, so we'll trial and error as we go). I've
added a parameter into the query that will prompt you to enter a number (+1
for one year forward, -1 for one year backward).

SELECT Format(DateSerial(Year(Date())+[Enter the number of year(s) to go
forward (positive) or to go backward (negative):],[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

We may also need to modify the other DateSerial function within the query,
but let's just do this one first and see if it works.

--

Ken Snell
<MS ACCESS MVP>

I currently have a Query that converts [Meeting Week], [Meeting Day] and
[Meeting Month] from my Table into the proper date for the current year in
[Meeting Date] in my Query.

Meaning, I can have [1] (first), [2] (Monday) and [1] (in January) in my
Table and [Meeting Date] in my Query will tell me that it is "January 5,
2004."

However, I need to be able to modify my code to either make the appropriate
calculations one year previous or one year in the future depending on the
instance. While I know there are other ways to accomplish this

conversion,

I
am married to this method so I really need a way to modify this code to find
the given date for a given day of the week in a given month one year in the
future and one year in the past.

My current code reads as follows:

SELECT Format(DateSerial(Year(Date()),[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

What needs to be added, and where?

Thank you in advance.

 

[Ken Snell [MVP]]

Glad it worked for you!

--

Ken Snell
<MS ACCESS MVP>

Thank you so much. First, thank you for helping me originally (I'm the same
Ben). Second, thank you for helping me understand where the "+/-1" needed
to go and how to format it. I played around with different formats and with
different placements, but never got it to work. I made the change where you
suggested and that fixes the year. The further modification to the
DateSerial is required - I did it and everything is working like a dream. I
am in your debt. Cheers.

Hello Ben...

The code that you posted looks like some code that I'd posted to help Mario
and Ben (same person? same as you?) in early July 2004. So let me try to
expand this query's capabilities with you.

Let's try something and determine if it's what you need (I don't have your
table/data set up for testing here, so we'll trial and error as we go). I've
added a parameter into the query that will prompt you to enter a number (+1
for one year forward, -1 for one year backward).

SELECT Format(DateSerial(Year(Date())+[Enter the number of year(s) to go
forward (positive) or to go backward (negative):],[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

We may also need to modify the other DateSerial function within the query,
but let's just do this one first and see if it works.

--

Ken Snell
<MS ACCESS MVP>

I currently have a Query that converts [Meeting Week], [Meeting Day] and
[Meeting Month] from my Table into the proper date for the current

year

in

[Meeting Date] in my Query.

Meaning, I can have [1] (first), [2] (Monday) and [1] (in January) in my
Table and [Meeting Date] in my Query will tell me that it is "January 5,
2004."

However, I need to be able to modify my code to either make the appropriate
calculations one year previous or one year in the future depending on the
instance. While I know there are other ways to accomplish this

conversion,

I
am married to this method so I really need a way to modify this code

to
find

the given date for a given day of the week in a given month one year

in
the

future and one year in the past.

My current code reads as follows:

SELECT Format(DateSerial(Year(Date()),[Meeting
Month],8-DatePart("w",DateSerial(Year(Date()),[Meeting Month],1),1+[Meeting
Week Day] Mod 7)+([Meeting Week Num]-1)*7),"mmmm d"", ""yyyy") AS [Meeting
Date], *
FROM [Corporate List]
WHERE ((([Corporate List].[Annual Date]) Like "*January*") AND (([Corporate
List].[Annual Letter])=True) AND (([Corporate List].[Static Date]) Is
Null));

What needs to be added, and where?

Thank you in advance.