Amanda Walshaw
asked on
Microsoft Query - Microsoft Word Mail Merge
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.A LLOCATEDBA L 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.A LLOCATEDBA L-368 AS 'DISCOUNTED_BALANCE', DR_INVLINES.STOCKCODE, DR_INVLINES.LINETOTAL_INCT AX
FROM bledisloe_newzealand.dbo.D R_ACCS DR_ACCS, bledisloe_newzealand.dbo.D R_INVLINES DR_INVLINES, bledisloe_newzealand.dbo.D R_TRANS DR_TRANS, bledisloe_newzealand.dbo.J OBCOST_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 .ALLOCATED BAL>10 And DR_TRANS.AMOUNT-DR_TRANS.A LLOCATEDBA L<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='AA DM01') OR (DR_TRANS.AMOUNT-DR_TRANS. ALLOCATEDB AL>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='AA DM01'))
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
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.A
FROM bledisloe_newzealand.dbo.D
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.