Create Code to have a table field formatted

  • Thread starter Thread starter Sondra
  • Start date Start date
S

Sondra

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


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:

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


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:

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]));
 
Sondra said:
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]));
Click to expand...


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:

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 said:
Sondra said:
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]));
Click to expand...


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


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:

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.
 
Sondra said:
Dirk:

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


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:

Only the time shows in the mailmerge. I'm usinhg the tbl_DSCRMerge for the
word document mailmerge process.

I've checked the formatting of the field in tbl_DSCRMerger and it is a
date/time field and the formatting is blank.

Any advise??

Thanks.

Sondra
 
Sondra said:
Dirk:

Only the time shows in the mailmerge. I'm usinhg the tbl_DSCRMerge for
the
word document mailmerge process.

I've checked the formatting of the field in tbl_DSCRMerger and it is a
date/time field and the formatting is blank.
Click to expand...


At this point I'm at a loss. *Something* is formatting the field as a time,
not as a date. My best guess now is that it's something in the options of
the mail-merge. One possibility is that there's a formatting option applied
to the merge field in the Word document. I'm not an expert on Word, but I
gather that you can select the merge fields in the document and press
Shift+F9 to see their coding. Then you can add or remove a format string.
See the discussion thread at this link:

http://www.microsoft.com/office/com...dd6841-3608-43d1-8509-eee097d736c2&sloc=en-us


That would be one possibility.
 
Back
Top