Solved

Microsoft Query - Microsoft Word Mail Merge

Posted on 2013-06-30
1
471 Views
Last Modified: 2013-08-15
I am running the following query in microsoft word
SELECT DR_TRANS.BRANCHNO, DR_TRANS.TRANSTYPE, DR_ACCS.NAME, DR_ACCS.ADDRESS1, DR_ACCS.ADDRESS2, DR_ACCS.ADDRESS3, DR_ACCS.ADDRESS4, DR_ACCS.POST_CODE, JOBCOST_HDR.SERIALNO, DR_TRANS.TRANSDATE, DR_TRANS.TRANSDATE+14 AS 'DR_TRANS.TRANSDATE14', DR_TRANS.TRANSDATE+21 AS 'DR_TRANS.TRANSDATE21', DR_TRANS.AMOUNT-DR_TRANS.ALLOCATEDBAL AS 'TRANBALANCE', DR_TRANS.AMOUNT, DR_TRANS.ALLOCATEDBAL, DR_ACCS.ACCNO, DR_TRANS.INVNO, DR_TRANS.JOBNO, JOBCOST_HDR.JOBCODE, DR_TRANS.AMOUNT-DR_TRANS.ALLOCATEDBAL-368 AS 'DISCOUNTED_BALANCE', DR_INVLINES.STOCKCODE, DR_INVLINES.LINETOTAL_INCTAX
FROM bledisloe_newzealand.dbo.DR_ACCS DR_ACCS, bledisloe_newzealand.dbo.DR_INVLINES DR_INVLINES, bledisloe_newzealand.dbo.DR_TRANS DR_TRANS, bledisloe_newzealand.dbo.JOBCOST_HDR JOBCOST_HDR
WHERE DR_TRANS.ACCNO = DR_ACCS.ACCNO AND JOBCOST_HDR.JOBNO = DR_TRANS.JOBNO AND JOBCOST_HDR.ACCNO = DR_ACCS.ACCNO AND DR_TRANS.INVNO = DR_INVLINES.INVNO AND DR_INVLINES.TRANSDATE = DR_TRANS.TRANSDATE AND ((DR_TRANS.AMOUNT-DR_TRANS.ALLOCATEDBAL>10 And DR_TRANS.AMOUNT-DR_TRANS.ALLOCATEDBAL<229) AND (DR_TRANS.BRANCHNO>812 And DR_TRANS.BRANCHNO<999) AND (DR_TRANS.TRANSTYPE=1) AND (DR_ACCS.BALANCE>10) AND (DR_TRANS.TRANSDATE+14={ts '2013-06-14 00:00:00'}) AND (DR_INVLINES.STOCKCODE='AADM01') OR (DR_TRANS.AMOUNT-DR_TRANS.ALLOCATEDBAL>231) AND (DR_TRANS.BRANCHNO>812 And DR_TRANS.BRANCHNO<999) AND (DR_TRANS.TRANSTYPE=1) AND (DR_ACCS.BALANCE>10) AND (DR_TRANS.TRANSDATE+14={ts '2013-06-14 00:00:00'}) AND (DR_INVLINES.STOCKCODE='AADM01'))
ORDER BY DR_TRANS.BRANCHNO


in the letter when it merges the date format is coming out as


28/06/2013 12:00:00 AM


Invoice No:      9XXXX / XXXX
Ref:            XXXXXXXX



Public Trust
Private Bag XXXX
Auckland 1500
Attn Tania XXXX



Dear Sir / Madam

RE:  FUNERAL ACCOUNT FOR THE LATE
MR ROBERT XXXXXXXX
BALANCE OUTSTANDING: $XXXXXX

You will have recently received the above account showing a reduced amount of $XXXX if payment is made by 5/07/2013 12:00:00 AM.




Note I need to change the date format  from 05/07/2013 12:00:00AM to 05/07/2013,
to 28/06/2013 12:00:00 AM 28/06/2013.

Thanks
0
Comment
Question by:Amanda Walshaw
1 Comment
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39289007
The will give you dd/mm/yyyy format, but I' not sure which column(s) you are using

, convert(varchar, DR_TRANS.TRANSDATE ,103)    
, convert(varchar, DR_TRANS.TRANSDATE + 14 ,103)    
, convert(varchar, DR_TRANS.TRANSDATE + 21 ,103)    

this date format pattern does not display time, just dd/mm/yyyy
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

930 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

8 Experts available now in Live!

Get 1:1 Help Now