Link to home
Start Free TrialLog in
Avatar of baggsh
baggsh

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Nosterdamus
Nosterdamus
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
Avatar of baggsh
baggsh

ASKER

Nosterdamus....can you expand your response...I do not follow what you mean.

Nico....similarly I do understand your comments
thanks
Avatar of baggsh

ASKER

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
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
Avatar of Asta Cu
Has this helped you, or is more needed?
Asta
Avatar of baggsh

ASKER

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
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

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

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

Regards,

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

 - Answered by: Nosterdamus

Nic;o)
Per recommendation, force-accepted.

Netminder
CS Moderator