Date Format Conversion

I have a list of clients in an Access database. Each client has a meeting
that takes place on a specific day of a given month each year: e.g., "second
Monday in July." I am creating a Mail Merge in Word from this database. I
need to have "second Monday in July" merge as "July 12, 2004" this year and
be able to merge as "July 11, 2005" next year, etc. I am teaching myself
Access, and Mail Merge (with IF/THEN functions), and have a very limited
understanding of VB and Access. Please dumb down any explanation for me.

How do you store this "second Monday in July" data in your table? If it were
me, I'd store this info in three fields (one record for each month that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday =1, Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA or in a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 - DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod 7) +
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has a meeting
that takes place on a specific day of a given month each year: e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this database.
I
need to have "second Monday in July" merge as "July 12, 2004" this year
and
be able to merge as "July 11, 2005" next year, etc. I am teaching myself
Access, and Mail Merge (with IF/THEN functions), and have a very limited
understanding of VB and Access. Please dumb down any explanation for me.

Not to be a total moron, but where would this VBA go in the query? You just
open the query and launch VB to make a module? Would the data format be
changed in the query and then the proper values are put into the mail merge?

So the main Database has "7 " "2" "2" - the query makes it "July" and the
"11" would show up in which field? - what happens to the data in the
"MeetingWeekNum" field when you do the mail merge?

Thanks.


"Ken Snell" wrote in message
...
How do you store this "second Monday in July" data in your table? If it
were
me, I'd store this info in three fields (one record for each month that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday =1,
Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA or in a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 - DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod 7) +
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has a
meeting
that takes place on a specific day of a given month each year: e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this database.
I
need to have "second Monday in July" merge as "July 12, 2004" this year
and
be able to merge as "July 11, 2005" next year, etc. I am teaching
myself
Access, and Mail Merge (with IF/THEN functions), and have a very limited
understanding of VB and Access. Please dumb down any explanation for
me.

None of what I've presented has to be done in VBA. It can be used directly
in a query. What you would do is create a calculated field in the query that
is named the MeetingDate, and use the expression as presented (surround the
"variables" with [ ] characters to designate them as fields from your
table).

Thus, the calculated field would be this:

MeetingDate: DateSerial(Year(Date()), [MeetingMonth], 8 - DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7)

As noted earlier, this assumes that you have the three fields in your table.
Let me expand a bit now on what I would see as the full table record:
MeetingID
MeetingMonth
MeetingWeekDay
MeetingWeekNum
MeetingYear

I've added MeetingYear if you want to specify the year in which the meeting
would occur, instead of using the current date to get the year. In that
case, the calculated field would be

MeetingDate: DateSerial([MeetingYear], [MeetingMonth], 8 - DatePart("w",
DateSerial([MeetingYear], [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
MS ACCESS MVP


"Ben" wrote in message
...
Not to be a total moron, but where would this VBA go in the query? You
just
open the query and launch VB to make a module? Would the data format be
changed in the query and then the proper values are put into the mail
merge?

So the main Database has "7 " "2" "2" - the query makes it "July" and the
"11" would show up in which field? - what happens to the data in the
"MeetingWeekNum" field when you do the mail merge?

Thanks.


"Ken Snell" wrote in message
...
How do you store this "second Monday in July" data in your table? If it
were
me, I'd store this info in three fields (one record for each month that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday =1,
Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA or in
a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 -
DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod 7) +
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has a
meeting
that takes place on a specific day of a given month each year: e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this
database.
I
need to have "second Monday in July" merge as "July 12, 2004" this
year
and
be able to merge as "July 11, 2005" next year, etc. I am teaching
myself
Access, and Mail Merge (with IF/THEN functions), and have a very
limited
understanding of VB and Access. Please dumb down any explanation for
me.

Okay, when you say "create a calculated field in the query," what do you
mean? Is it a column you put in your table and then include in your query?
I still don't understand where you type in the calculation. Do you do it in
the table itself or the query? In the cell, in design view, where/how? I'm
sorry I am not getting this. I've tried to apply it, but I'm unsure of
where I'm putting the calcualtion. Thanks.


"Ken Snell" wrote in message
...
None of what I've presented has to be done in VBA. It can be used directly
in a query. What you would do is create a calculated field in the query
that
is named the MeetingDate, and use the expression as presented (surround
the
"variables" with [ ] characters to designate them as fields from your
table).

Thus, the calculated field would be this:

MeetingDate: DateSerial(Year(Date()), [MeetingMonth], 8 - DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7)

As noted earlier, this assumes that you have the three fields in your
table.
Let me expand a bit now on what I would see as the full table record:
MeetingID
MeetingMonth
MeetingWeekDay
MeetingWeekNum
MeetingYear

I've added MeetingYear if you want to specify the year in which the
meeting
would occur, instead of using the current date to get the year. In that
case, the calculated field would be

MeetingDate: DateSerial([MeetingYear], [MeetingMonth], 8 - DatePart("w",
DateSerial([MeetingYear], [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7)
+
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
MS ACCESS MVP


"Ben" wrote in message
...
Not to be a total moron, but where would this VBA go in the query? You
just
open the query and launch VB to make a module? Would the data format be
changed in the query and then the proper values are put into the mail
merge?

So the main Database has "7 " "2" "2" - the query makes it "July" and
the
"11" would show up in which field? - what happens to the data in the
"MeetingWeekNum" field when you do the mail merge?

Thanks.


"Ken Snell" wrote in message
...
How do you store this "second Monday in July" data in your table? If
it
were
me, I'd store this info in three fields (one record for each month
that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above
fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday =1,
Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA or
in
a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 -
DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod 7) +
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has a
meeting
that takes place on a specific day of a given month each year: e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this
database.
I
need to have "second Monday in July" merge as "July 12, 2004" this
year
and
be able to merge as "July 11, 2005" next year, etc. I am teaching
myself
Access, and Mail Merge (with IF/THEN functions), and have a very
limited
understanding of VB and Access. Please dumb down any explanation
for
me.

A calculated field is a field that you put in a query when designing the
query... a calculated field is an expression that calculates a result. It
may or may not use one or more fields from the source tables.

Open your query in design view. Go to the first empty column. Paste the
entire expression (including the MeetingDate: part) into the "Field:" cell.

--

Ken Snell
MS ACCESS MVP

"Ben" wrote in message
...
Okay, when you say "create a calculated field in the query," what do you
mean? Is it a column you put in your table and then include in your
query?
I still don't understand where you type in the calculation. Do you do it
in
the table itself or the query? In the cell, in design view, where/how?
I'm
sorry I am not getting this. I've tried to apply it, but I'm unsure of
where I'm putting the calcualtion. Thanks.


"Ken Snell" wrote in message
...
None of what I've presented has to be done in VBA. It can be used
directly
in a query. What you would do is create a calculated field in the query
that
is named the MeetingDate, and use the expression as presented (surround
the
"variables" with [ ] characters to designate them as fields from your
table).

Thus, the calculated field would be this:

MeetingDate: DateSerial(Year(Date()), [MeetingMonth], 8 - DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7)
+
([MeetingWeekNum] - 1) * 7)

As noted earlier, this assumes that you have the three fields in your
table.
Let me expand a bit now on what I would see as the full table record:
MeetingID
MeetingMonth
MeetingWeekDay
MeetingWeekNum
MeetingYear

I've added MeetingYear if you want to specify the year in which the
meeting
would occur, instead of using the current date to get the year. In that
case, the calculated field would be

MeetingDate: DateSerial([MeetingYear], [MeetingMonth], 8 - DatePart("w",
DateSerial([MeetingYear], [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod
7)
+
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
MS ACCESS MVP


"Ben" wrote in message
...
Not to be a total moron, but where would this VBA go in the query? You
just
open the query and launch VB to make a module? Would the data format
be
changed in the query and then the proper values are put into the mail
merge?

So the main Database has "7 " "2" "2" - the query makes it "July" and
the
"11" would show up in which field? - what happens to the data in the
"MeetingWeekNum" field when you do the mail merge?

Thanks.


"Ken Snell" wrote in message
...
How do you store this "second Monday in July" data in your table? If
it
were
me, I'd store this info in three fields (one record for each month
that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above
fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday =1,
Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA or
in
a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 -
DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod 7)
+
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has a
meeting
that takes place on a specific day of a given month each year:
e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this
database.
I
need to have "second Monday in July" merge as "July 12, 2004" this
year
and
be able to merge as "July 11, 2005" next year, etc. I am teaching
myself
Access, and Mail Merge (with IF/THEN functions), and have a very
limited
understanding of VB and Access. Please dumb down any explanation
for
me.

Wow - thank you so much for the handholding, I got it working!!!



Now, if you'll indulge me, two more questions:



1.. To make the date merge into my letters in a given format, should I
alter the code in the query (I'm not sure how to do this), or the merge
field in Word (which I know how to do).
2.. Is there a way to have it convert the second Monday in December for
LAST year and NEXT year? How do you tweak the code to do that? In
December, I need to be able to calculate the meeting date for January of the
next year, and in January I need to be able to send out a letter referencing
the meeting that was held in December of the previous year.


Thanks.



"Ken Snell" wrote in message
...
A calculated field is a field that you put in a query when designing the
query... a calculated field is an expression that calculates a result. It
may or may not use one or more fields from the source tables.

Open your query in design view. Go to the first empty column. Paste the
entire expression (including the MeetingDate: part) into the "Field:"
cell.

--

Ken Snell
MS ACCESS MVP

"Ben" wrote in message
...
Okay, when you say "create a calculated field in the query," what do you
mean? Is it a column you put in your table and then include in your
query?
I still don't understand where you type in the calculation. Do you do it
in
the table itself or the query? In the cell, in design view, where/how?
I'm
sorry I am not getting this. I've tried to apply it, but I'm unsure of
where I'm putting the calcualtion. Thanks.


"Ken Snell" wrote in message
...
None of what I've presented has to be done in VBA. It can be used
directly
in a query. What you would do is create a calculated field in the
query
that
is named the MeetingDate, and use the expression as presented
(surround
the
"variables" with [ ] characters to designate them as fields from your
table).

Thus, the calculated field would be this:

MeetingDate: DateSerial(Year(Date()), [MeetingMonth], 8 -
DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod
7)
+
([MeetingWeekNum] - 1) * 7)

As noted earlier, this assumes that you have the three fields in your
table.
Let me expand a bit now on what I would see as the full table record:
MeetingID
MeetingMonth
MeetingWeekDay
MeetingWeekNum
MeetingYear

I've added MeetingYear if you want to specify the year in which the
meeting
would occur, instead of using the current date to get the year. In
that
case, the calculated field would be

MeetingDate: DateSerial([MeetingYear], [MeetingMonth], 8 -
DatePart("w",
DateSerial([MeetingYear], [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod
7)
+
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
MS ACCESS MVP


"Ben" wrote in message
...
Not to be a total moron, but where would this VBA go in the query?
You
just
open the query and launch VB to make a module? Would the data format
be
changed in the query and then the proper values are put into the
mail
merge?

So the main Database has "7 " "2" "2" - the query makes it "July"
and
the
"11" would show up in which field? - what happens to the data in the
"MeetingWeekNum" field when you do the mail merge?

Thanks.


"Ken Snell" wrote in message
...
How do you store this "second Monday in July" data in your table?
If
it
were
me, I'd store this info in three fields (one record for each month
that
applies):
MeetingMonth
MeetingWeekDay
MeetingWeekNum

So "second Monday in July" would mean these values in the above
fields:
MeetingMonth would be 7 (for July)
MeetingWeekDay would be 2 (for Monday -- Sunday
=1,
Monday
=2, etc.)
MeetingWeekNum would be 2 (for second one of month)

Then, the correct date could be calculated via an expression (VBA
or
in
a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 -
DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod
7)
+
(MeetingWeekNum - 1) * 7)

--

Ken Snell
MS ACCESS MVP

"Mario" wrote in message
...
I have a list of clients in an Access database. Each client has
a
meeting
that takes place on a specific day of a given month each year:
e.g.,
"second
Monday in July." I am creating a Mail Merge in Word from this
database.
I
need to have "second Monday in July" merge as "July 12, 2004"
this
year
and
be able to merge as "July 11, 2005" next year, etc. I am
teaching
myself
Access, and Mail Merge (with IF/THEN functions), and have a very
limited
understanding of VB and Access. Please dumb down any
explanation
for
me.

Comments inline...

--

Ken Snell
MS ACCESS MVP

"Ben" wrote in message
...
Wow - thank you so much for the handholding, I got it working!!!



Now, if you'll indulge me, two more questions:



1.. To make the date merge into my letters in a given format, should I
alter the code in the query (I'm not sure how to do this), or the merge
field in Word (which I know how to do).

I have not worked with merging into Word, so my answer may be completely
wrong. However, if the field in Word is just expecting a text string, then
you can wrap the expression with the Format function to specify how to
export the date -- for example, to get "January 1, 2004" format:

MeetingDate: Format(DateSerial(Year(Date()), [MeetingMonth], 8 -
DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7), "mmmm dd, yyyy")


2.. Is there a way to have it convert the second Monday in December for
LAST year and NEXT year? How do you tweak the code to do that? In
December, I need to be able to calculate the meeting date for January of
the
next year, and in January I need to be able to send out a letter
referencing
the meeting that was held in December of the previous year.

This field can calculate only one value; it cannot give you two. However,
you can put a second calculated field in the query that will give you the
other date. So, for example, let's have two calculated fields, one for the
next meeting date, and one for the previous meeting date:

NextMeetingDate: Format(DateSerial(Year(Date() - (Month(Date())=12)),
[MeetingMonth], 8 - DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7), "mmmm dd, yyyy")

PreviousMeetingDate: Format(DateSerial(Year(Date() + (Month(Date())=1)),
[MeetingMonth], 8 - DatePart("w",
DateSerial(Year(Date()), [MeetingMonth], 1), 1 + [MeetingWeekDay] Mod 7) +
([MeetingWeekNum] - 1) * 7), "mmmm dd, yyyy")

The above fields use a boolean expression (e.g., Month(Date())=12 ) to
return a True or False. True in ACCESS is the integer value of -1 (negative
1). So, for a December date, the expression adds 1 to the year value. The
other one uses similar expression to subtract one from the year value.



Thanks.