Solved

addressing a word stopTAB stop in VBA

Posted on 2002-05-26
14
221 Views
Last Modified: 2006-11-17
I have merged the results of an access 2000 query in a Word document. The word document has several columns ie. a particulars , a debit and a credit column for figures. My problem is that unless I use non proportional fonts I get the figures columns snaking down the page in a very untidy column (obviously affected by the size of the letters in the particulars). I wonder if this problem can be overcome by addressing the tabs stops in word from within the query. I would also have to have the figures decimal aligned.
I would be gratefull for any suggestions and especially if anyone has the correct code or sample database where such occurs
Thanks
0
Comment
Question by:baggsh
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 200 total points
ID: 7035174
Hi baggsh,

I suggest that you do this with the Mail Merge mechanism. This will allow you to set whatever format you want, and let the mail merge print the query result (set the query as the input DB for the Mail Merge...).

Hope this helps,

Nosterdamus
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7035225
Does the placement of a decimal tab in your debit and credit column help?
You can ofcourse us the valuta format to get the query to show the figures with the right number of decimals.

Nic;o)
0
 

Author Comment

by:baggsh
ID: 7036404
Nosterdamus....can you expand your response...I do not follow what you mean.

Nico....similarly I do understand your comments
thanks
0
 

Author Comment

by:baggsh
ID: 7036410
The access query IS the data source however to position the money columns in an exact column position involves counting spaces and characters as follows:

CalPrivateContrac: IIf(Not [ClientData]![PrivateContractFee]=0,"Fee for Private Contract preparation *" & Space$(45-38) & "$" & Space$(12-Len(Format$(CStr([PrivateContractFee]),"Fixed"))) & Format$([PrivateContractFee],"Fixed") & Chr(10),"")

This code for one item in the query allows for the words "Fee for Private contract" to appear in the left column and an amount in the debit column and nothing in the credit column (Column on the right hand side of the page

It would appear to me that if I can specify in code that the amount is inserted at one of the two Tab stops in the merged word document this would be a better option
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 7036584
Hi baggsh,

What I mean is that yo can use MS Word's Mail Merge to print out at the correct format.

First, set the desired query in MS Access (should contain 3 columns?). Then, open MS Word, and set a Mail Merge document. Set the document's data source to the MS Access DB and select the query you've just built. Place the fields in the desired places (in MS Word Document) and save. You can also include additional info, such as: printing time, headers, footers etc,.

Now, every time that you activate the Mail Merge (from MS Word), the query will be executed and will return the correct values to print.

This way you can enjoy both worlds: DB capabilities of MS Access and printing capabilities of MS Word.

HTH,

Nosterdamus
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7150399
Has this helped you, or is more needed?
Asta
0
 

Author Comment

by:baggsh
ID: 7156823
I am still struggling with the concept. In the word document which I call a statement the items appear in a debit or a credit column depending on the results of the query. I am not sure how I can enable this to happen by placing the fields on a report (which would have to be in the debit and credit columns) and then direct the word document to accept only one of the two figures in only one of the columns.
I also have the problem that if I place a field on the Word document and the query does not have a result for that filed...ie it its meant to be omiited altogether- I have a blank line on the page which makes the statement look pretty awful!
So yes any more help would be appreciated...I think Nosterdamus is close but not fully understanding my problem.
Many thanks
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:Nosterdamus
ID: 7159076
Hi baggsh,

After you've set up the data source file for the Mail Merge, you can insert two kinds of fields into your document:
1. A field from your source DB.
2. A Word Field, which can allow you to define specific printing behaviors when a defined situation occurs in your source DB.

After you've completed to design the Master Document, open the Mail Merge menu and select option 3 (Merge...). At the dialog form that opens you can specify if you want word to print empty rows, or not.

I suggest that you play around with the various features (including searching the MS Word help for detailed explanations and examples) before setting up the final master document.

HTH,

Nosterdamus
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7226998

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 7227879
I don't think so nico.

IMO, if baggsh will not return, then I should get the points.

Regards,

Nosterdamus
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7228043
Hi Nosterdamus,

I got the feeling that the Q wasn't really finished.
Just try to imagine how valuable the line of comment is for a question buyer paying 20 pts to see the solution.
If you're still convinced it can be found, I'll change my recommandation.
(What really is only a recommandation, as the moderators always have the last word)

Nic;o)
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 7228194
Hi nico,

I know that your motives are pure, as you are doing an excelent job keeping this TA in tip-top condition. Moreover I never-ever had any disagreements with you regarding the recommendations that you've posted, including in many questions that I took part.

In this particular question, I think that your judgement tricked you... as baggsh asked a question regarding "addressing a word stop TAB stop in VBA ". It's true the solution proposes does NOT solve the problem as it was asked, but give the asker a hole new way of attacking the problem.

Sometimes questioners will pay 20 pts for a question they think they need answer about, just to see an answer like "Sorry, this can't be done" or something a like.

The solution I provided, do work and does solve the problem in question (although not directly), and for that I think I'd pay 20 pts, whouldn't you?

Sincerely,

Nosterdamus ;-)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7228288
I agree ;-)
I'll changed my recommandation to:

 - Answered by: Nosterdamus

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7248034
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now