Date Format Conversion

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

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)
 
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.
 
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)
 
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 said:
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 said:
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.


it
were
Click to expand...
in
a for
me.
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
7)
+
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
<MS ACCESS MVP>


Ben said:
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.


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
Click to expand...
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>

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
query
that
is named the MeetingDate, and use the expression as presented
Click to expand...
(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
Click to expand...
Click to expand...
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
Click to expand...
7)
+
([MeetingWeekNum] - 1) * 7)


--

Ken Snell
<MS ACCESS MVP>


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"
Click to expand...
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.


How do you store this "second Monday in July" data in your table?
Click to expand...
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
Click to expand...
or
in
a
query) similar to this:
MeetingDate = DateSerial(Year(Date()), MeetingMonth, 8 -
DatePart("w",
DateSerial(Year(Date()), MeetingMonth, 1), 1 + MeetingWeekDay Mod
Click to expand...
Click to expand...
7)
+ explanation
for
Click to expand...
Click to expand...
 
Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Ben said:
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).
Click to expand...

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.
Click to expand...

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.
 
Back
Top