Re: Date Format Conversion

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ken Snell (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 07/07/04 

Date: Tue, 6 Jul 2004 20:49:38 -0400

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" <ben@ben.com> wrote in message
news:%23iCt$D4YEHA.2480@tk2msftngp13.phx.gbl...
> 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" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> news:eHG4zNKYEHA.2940@TK2MSFTNGP09.phx.gbl...
> > 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" <ben@ben.com> wrote in message
> > news:Ov7OnnEYEHA.2216@TK2MSFTNGP10.phx.gbl...
> > > 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" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> > > news:euqdxC9XEHA.1684@tk2msftngp13.phx.gbl...
> > > > 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" <mario@mario.com> wrote in message
> > > > news:eyGgdD8XEHA.2940@TK2MSFTNGP09.phx.gbl...
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Quantcast