• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

addressing a word stopTAB stop in VBA

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
baggsh
Asked:
baggsh
  • 5
  • 4
  • 3
  • +2
1 Solution
 
NosterdamusCommented:
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
 
nico5038Commented:
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
 
baggshAuthor Commented:
Nosterdamus....can you expand your response...I do not follow what you mean.

Nico....similarly I do understand your comments
thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
baggshAuthor Commented:
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
 
NosterdamusCommented:
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
 
Asta CuTechnical consultant & graphic designCommented:
Has this helped you, or is more needed?
Asta
0
 
baggshAuthor Commented:
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
 
NosterdamusCommented:
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
 
nico5038Commented:

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
 
NosterdamusCommented:
I don't think so nico.

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

Regards,

Nosterdamus
0
 
nico5038Commented:
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
 
NosterdamusCommented:
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
 
nico5038Commented:
I agree ;-)
I'll changed my recommandation to:

 - Answered by: Nosterdamus

Nic;o)
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now