Using Access 2002

I have created the following to use in a WordMerge Document:

Private Sub Form_Close()
DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
DoCmd.OpenForm "DSCR_frm"
End Sub

However, I want to add a line to have a field in the Table formatted so that
the number:

83175 shows as 08D-3175

I know that if I go into the table after this is run each time I can do
this; however, is there a way to add that to the above programming so that it
automatically does it before the User closes the database.

I'm very novice and write very "easy" processes. Any help would be great.

Thanks.

"Sondra" <(E-Mail Removed)> wrote in message
news:721DC322-CF3E-43BC-A839-(E-Mail Removed)...
> Using Access 2002
>
> I have created the following to use in a WordMerge Document:
>
> Private Sub Form_Close()
> DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
> DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
> DoCmd.OpenForm "DSCR_frm"
> End Sub
>
> However, I want to add a line to have a field in the Table formatted so
> that
> the number:
>
> 83175 shows as 08D-3175
>
> I know that if I go into the table after this is run each time I can do
> this; however, is there a way to add that to the above programming so that
> it
> automatically does it before the User closes the database.
>
> I'm very novice and write very "easy" processes. Any help would be great.


Am I right in concluding that qry_DSCRForm_RUN is make-table query that
creates the table tbl_DSCRMERGE? I'll continue based on that assumption.

Do you need the field's original number value to be preserved, in the output
table, but just formatted so that it appears the way you describe? Or would
it be okay if it is converted into a text field with actual values like
"08D-03175"?

If the latter is the case, then you could do your formatting in the query to
create a calculated field. Suppose, for example you have input table
"tbl_Input", with fields "ID" and "MyNumber", and it's the MyNumber field
that you want to be formatted in the output table. Then your make-table
query might look like this:

SELECT
tbl_Input.ID,
Format(tbl_Input.MyNumber,"00D-0000") AS MyNumber
INTO tbl_DSCRMERGE
FROM tbl_Input;

That's only an example, and I don't know for sure if that format expression
will work for all the values in your source data.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk:

Thank you for your help. Here is the process:

The user enters data into form "frm_DSCRDeferral" when done they close the
form and choose close. Then my expression runs:

Private Sub Form_Close()
DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
DoCmd.OpenForm "DSCR_frm"
End Sub

The user closes the database, opens word and merges the table
"tbl_DSCRMERGE" into Word.

I definitely would prefer to have the field converted before they close
Access. I believe I understand your instructions, but I'm not exactly sure
where to put this. As I said earlier, I'm very novice and write very simple
little "expressions." Any guidance would be additionally helpful.

Here is my information:

Make Table: DSCRMerge
Query to Make Table: qry_DSCRForm_Run
Field in Query: DSCRNumber
Field in MakeTable: DSCRNumber

Thanks again and sorry to sound so confused.




"Dirk Goldgar" wrote:

> "Sondra" <(E-Mail Removed)> wrote in message
> news:721DC322-CF3E-43BC-A839-(E-Mail Removed)...
> > Using Access 2002
> >
> > I have created the following to use in a WordMerge Document:
> >
> > Private Sub Form_Close()
> > DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
> > DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
> > DoCmd.OpenForm "DSCR_frm"
> > End Sub
> >
> > However, I want to add a line to have a field in the Table formatted so
> > that
> > the number:
> >
> > 83175 shows as 08D-3175
> >
> > I know that if I go into the table after this is run each time I can do
> > this; however, is there a way to add that to the above programming so that
> > it
> > automatically does it before the User closes the database.
> >
> > I'm very novice and write very "easy" processes. Any help would be great.
>
>
> Am I right in concluding that qry_DSCRForm_RUN is make-table query that
> creates the table tbl_DSCRMERGE? I'll continue based on that assumption.
>
> Do you need the field's original number value to be preserved, in the output
> table, but just formatted so that it appears the way you describe? Or would
> it be okay if it is converted into a text field with actual values like
> "08D-03175"?
>
> If the latter is the case, then you could do your formatting in the query to
> create a calculated field. Suppose, for example you have input table
> "tbl_Input", with fields "ID" and "MyNumber", and it's the MyNumber field
> that you want to be formatted in the output table. Then your make-table
> query might look like this:
>
> SELECT
> tbl_Input.ID,
> Format(tbl_Input.MyNumber,"00D-0000") AS MyNumber
> INTO tbl_DSCRMERGE
> FROM tbl_Input;
>
> That's only an example, and I don't know for sure if that format expression
> will work for all the values in your source data.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"Sondra" <(E-Mail Removed)> wrote in message
news:3E265540-F568-42E3-80E5-(E-Mail Removed)...
> Dirk:
>
> Thank you for your help. Here is the process:
>
> The user enters data into form "frm_DSCRDeferral" when done they close the
> form and choose close. Then my expression runs:
>
> Private Sub Form_Close()
> DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
> DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
> DoCmd.OpenForm "DSCR_frm"
> End Sub
>
> The user closes the database, opens word and merges the table
> "tbl_DSCRMERGE" into Word.
>
> I definitely would prefer to have the field converted before they close
> Access. I believe I understand your instructions, but I'm not exactly
> sure
> where to put this. As I said earlier, I'm very novice and write very
> simple
> little "expressions." Any guidance would be additionally helpful.
>
> Here is my information:
>
> Make Table: DSCRMerge
> Query to Make Table: qry_DSCRForm_Run
> Field in Query: DSCRNumber
> Field in MakeTable: DSCRNumber
>
> Thanks again and sorry to sound so confused.


Don't apologize; we all help each other when we need it.

Please post the SQL view of qry_DSCRForm_Run. I propose to modify it, but I
need to know what it currently says.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk:

Here is my sql view:

SELECT DSCR.DSCRYear, DSCR.DSCRNumber, DSCR.DonorLastName,
DSCR.DonorFirstName, DSCR.DonorMiddleInitial, DSCR.DonorDOB, DSCR.DID,
DSCR.WBN, DSCR.DateofCollection, tbl_region.Region, DSCR.Comments, DSCR.Date,
DSCR.Initials INTO tbl_DSCRMERGE
FROM tbl_region INNER JOIN DSCR ON tbl_region.Regionid = DSCR.[Region Code]
WHERE (((DSCR.Date)=[Enter Date]) AND ((DSCR.Initials)=[Enter Initials]));


"Dirk Goldgar" wrote:

> "Sondra" <(E-Mail Removed)> wrote in message
> news:3E265540-F568-42E3-80E5-(E-Mail Removed)...
> > Dirk:
> >
> > Thank you for your help. Here is the process:
> >
> > The user enters data into form "frm_DSCRDeferral" when done they close the
> > form and choose close. Then my expression runs:
> >
> > Private Sub Form_Close()
> > DoCmd.DeleteObject acTable, "tbl_DSCRMERGE"
> > DoCmd.OpenQuery "qry_DSCRForm_RUN", acViewNormal
> > DoCmd.OpenForm "DSCR_frm"
> > End Sub
> >
> > The user closes the database, opens word and merges the table
> > "tbl_DSCRMERGE" into Word.
> >
> > I definitely would prefer to have the field converted before they close
> > Access. I believe I understand your instructions, but I'm not exactly
> > sure
> > where to put this. As I said earlier, I'm very novice and write very
> > simple
> > little "expressions." Any guidance would be additionally helpful.
> >
> > Here is my information:
> >
> > Make Table: DSCRMerge
> > Query to Make Table: qry_DSCRForm_Run
> > Field in Query: DSCRNumber
> > Field in MakeTable: DSCRNumber
> >
> > Thanks again and sorry to sound so confused.
>
>
> Don't apologize; we all help each other when we need it.
>
> Please post the SQL view of qry_DSCRForm_Run. I propose to modify it, but I
> need to know what it currently says.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"Sondra" <(E-Mail Removed)> wrote in message
news:2BFC954A-E139-42E9-82ED-(E-Mail Removed)...
> Dirk:
>
> Here is my sql view:
>
> SELECT DSCR.DSCRYear, DSCR.DSCRNumber, DSCR.DonorLastName,
> DSCR.DonorFirstName, DSCR.DonorMiddleInitial, DSCR.DonorDOB, DSCR.DID,
> DSCR.WBN, DSCR.DateofCollection, tbl_region.Region, DSCR.Comments,
> DSCR.Date,
> DSCR.Initials INTO tbl_DSCRMERGE
> FROM tbl_region INNER JOIN DSCR ON tbl_region.Regionid = DSCR.[Region
> Code]
> WHERE (((DSCR.Date)=[Enter Date]) AND ((DSCR.Initials)=[Enter Initials]));


You haven't said, but I'll assume that DSCRNumber is the field you want to
format. Try this and see if it gives you what you want:

SELECT
DSCR.DSCRYear,
Format(DSCR.DSCRNumber, "00D-0000") AS DSCRNumber,
DSCR.DonorLastName,
DSCR.DonorFirstName,
DSCR.DonorMiddleInitial,
DSCR.DonorDOB, DSCR.DID,
DSCR.WBN,
DSCR.DateofCollection,
tbl_region.Region,
DSCR.Comments,
DSCR.Date,
DSCR.Initials
INTO tbl_DSCRMERGE
FROM tbl_region INNER JOIN DSCR
ON tbl_region.Regionid = DSCR.[Region Code]
WHERE ((DSCR.Date=[Enter Date])
AND (DSCR.Initials=[Enter Initials]));


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk:

This is awesome. I only have one problem. When the DateofCollection comes
over it transfers into Word as time. How do I format that field to show
mm/dd/yyyy?

Thanks for your help.

"Dirk Goldgar" wrote:

> "Sondra" <(E-Mail Removed)> wrote in message
> news:2BFC954A-E139-42E9-82ED-(E-Mail Removed)...
> > Dirk:
> >
> > Here is my sql view:
> >
> > SELECT DSCR.DSCRYear, DSCR.DSCRNumber, DSCR.DonorLastName,
> > DSCR.DonorFirstName, DSCR.DonorMiddleInitial, DSCR.DonorDOB, DSCR.DID,
> > DSCR.WBN, DSCR.DateofCollection, tbl_region.Region, DSCR.Comments,
> > DSCR.Date,
> > DSCR.Initials INTO tbl_DSCRMERGE
> > FROM tbl_region INNER JOIN DSCR ON tbl_region.Regionid = DSCR.[Region
> > Code]
> > WHERE (((DSCR.Date)=[Enter Date]) AND ((DSCR.Initials)=[Enter Initials]));
>
>
> You haven't said, but I'll assume that DSCRNumber is the field you want to
> format. Try this and see if it gives you what you want:
>
> SELECT
> DSCR.DSCRYear,
> Format(DSCR.DSCRNumber, "00D-0000") AS DSCRNumber,
> DSCR.DonorLastName,
> DSCR.DonorFirstName,
> DSCR.DonorMiddleInitial,
> DSCR.DonorDOB, DSCR.DID,
> DSCR.WBN,
> DSCR.DateofCollection,
> tbl_region.Region,
> DSCR.Comments,
> DSCR.Date,
> DSCR.Initials
> INTO tbl_DSCRMERGE
> FROM tbl_region INNER JOIN DSCR
> ON tbl_region.Regionid = DSCR.[Region Code]
> WHERE ((DSCR.Date=[Enter Date])
> AND (DSCR.Initials=[Enter Initials]));
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"Sondra" <(E-Mail Removed)> wrote in message
news:6EAB28E5-1FB7-45DA-9E8A-(E-Mail Removed)...
> Dirk:
>
> This is awesome. I only have one problem. When the DateofCollection
> comes
> over it transfers into Word as time. How do I format that field to show
> mm/dd/yyyy?


I need to know better what you mean by "it transfers into Word as time."
What are you seeing in Word? Dates and times; e.g., "8/25/2008 3:22 PM"?
Or something else?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk:

In the access table (tbl_DSCRMERGE) it shows 08/21/2008; however, when I
merge into Word it show 12:00 a.m.

Hope that is a better explanation.

"Dirk Goldgar" wrote:

> "Sondra" <(E-Mail Removed)> wrote in message
> news:6EAB28E5-1FB7-45DA-9E8A-(E-Mail Removed)...
> > Dirk:
> >
> > This is awesome. I only have one problem. When the DateofCollection
> > comes
> > over it transfers into Word as time. How do I format that field to show
> > mm/dd/yyyy?
>
>
> I need to know better what you mean by "it transfers into Word as time."
> What are you seeing in Word? Dates and times; e.g., "8/25/2008 3:22 PM"?
> Or something else?
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"Sondra" <(E-Mail Removed)> wrote in message
news:1FE6F1C4-EAB0-487F-897A-(E-Mail Removed)...
> Dirk:
>
> In the access table (tbl_DSCRMERGE) it shows 08/21/2008; however, when I
> merge into Word it show 12:00 a.m.


So you're saying that no date is shown, only the time? Huh. 12:00 AM is
the time that is stored in a date/time field when only the date was assigned
to it. All date/time fields have both a date and time; if no time is
specified, the time is 12:00 AM.

I have no idea why a date field merged into Word is coming showing the time
only. Please check tbl_DSCRMERGE and make sure that the field in question
is truly a date/time field. Also make sure that its Format property is
blank.

How are you performing the merge? Is this a standard Word mail-merge, or
are you doing something more compilcated using code? Is tbl_DSCMERGE the
actual data source, or are you merging from a query?


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)