Print Account Number on Vendor Checks

Add to the Head Query, below SELECT;

vend_accntnum

and below FROM;

vend

pay attention to commas.

Create a new field and edit its property;

in the "Query source:" select Head
in the"Column field:" type vend_accntnum

And you are set.....

If anyone interested in a MICR checks printing on a blank checks paper with POSTBOOKS? let me know,
I'll post how-to do this by modifying only a Query.

Thanks for the response. I'm having trouble figuring out exactly which select statement add to. The query for this form is somewhat complicated, and I'm just getting familiar with SQL. Would the line marked with --> be correct?

SELECT
checkhead_id, checkhead_number, checkhead_for AS memo,
formatDate(checkhead_checkdate) AS f_checkdate,
formatMoney(checkhead_amount) AS f_amount, INITCAP(spellAmount(checkhead_amount, curr_id)) AS f_words, CASE WHEN(checkhead_void) THEN TEXT('V O I D')
ELSE TEXT('')
END AS f_void,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_number
FROM custinfo
WHERE (cust_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_code
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
--> WHEN checkhead_recip_type = 'V' THEN (SELECT vend_number, vend_accntnum
FROM vendinfo
WHERE (vend_id=checkhead_recip_id))
ELSE 'Unknown Recipient Type'
END AS recip_number,
formatAddr(CASE WHEN checkhead_recip_type = 'C' THEN
(SELECT cntct_addr_id
FROM cntct, custinfo
WHERE ((cust_cntct_id=cntct_id)
AND (cust_id=checkhead_recip_id)))
WHEN checkhead_recip_type = 'T' THEN
(SELECT taxauth_addr_id
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_addr_id
FROM vendaddrinfo
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_addr_id
FROM vendinfo
WHERE (vend_id=checkhead_recip_id)))
END) AS check_address,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_name
FROM custinfo
WHERE cust_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_name
FROM taxauth
WHERE taxauth_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_name
FROM vendaddr
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_name
FROM vendinfo
WHERE (vend_id=checkhead_recip_id))) END AS recip_name, curr_symbol, curr_abbr, curr_name FROM checkhead, curr_symbol WHERE ((checkhead_curr_id = curr_id)
AND (checkhead_id=<? value("checkhead_id") ?>) );

There is some errors in this query, and especially there is no vend_accntnum field in the vendinfo table:(
I've included an example query bellow, in this example you do not need to create a new field, existing field memo will print the account number.
------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
checkhead_id, checkhead_number, vend_accntnum AS memo,
formatDate(checkhead_checkdate) AS f_checkdate,
formatMoney(checkhead_amount) AS f_amount,
INITCAP(spellAmount(checkhead_amount, curr_id)) AS f_words,
CASE WHEN(checkhead_void) THEN TEXT('V O I D')
ELSE TEXT('')
END AS f_void,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_number
FROM custinfo
WHERE (cust_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_code
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'V' THEN (SELECT vend_number
FROM vendinfo
WHERE (vend_id=checkhead_recip_id))
ELSE 'Unknown Recipient Type'
END AS recip_number,
formatAddr(CASE WHEN checkhead_recip_type = 'C' THEN
(SELECT cntct_addr_id
FROM cntct, custinfo
WHERE ((cust_cntct_id=cntct_id)
AND (cust_id=checkhead_recip_id)))
WHEN checkhead_recip_type = 'T' THEN
(SELECT taxauth_addr_id
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_addr_id
FROM vendaddrinfo
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_addr_id
FROM vendinfo
WHERE (vend_id=checkhead_recip_id)))
END) AS check_address,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_name
FROM custinfo
WHERE cust_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_name
FROM taxauth
WHERE taxauth_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_name
FROM vendaddr
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_name
FROM vendinfo
WHERE (vend_id=checkhead_recip_id)))
END AS recip_name,
curr_symbol, curr_abbr, curr_name
FROM vend, checkhead, curr_symbol
WHERE ((checkhead_curr_id = curr_id) AND (checkhead_id=<? value("checkhead_id") ?>) );

OK,
First I tried edititing the original query to reflect the changes you indicated and that resulted in the check printing 84 pages of the check 3 on a page. I then tried completely wiping out the original query and pasting your recomendation in its entirty with the same results. I reverted back to the previous grade report and the check prints fine minus the account number.

Looking at the output, it appears that the 84 pages is actually 84 copies of the check Each copy of the check has a different account number, one for each of the 84 vendors in my test database that I am working. It prints 84 copies of the check, followed by 84 copies of the first stub, followed by 84 copies of the second stub. All of these 3 to a page.

I'm thinking I'm lacking a where clause somewhere in the query.

Any other suggestions? I'd be willing to export my entire report defination if you'd be willing to help??

Thank You...

My Current SQL is below. Anybody have any suggestions on how to correct this problem. Everythign I've tried just makes it worse. The account number does print on the check now, it just causes the problem with a check being printed for each and every vendor account number.

Thanks....

SELECT
vend_accntnum,
checkhead_id, checkhead_number, checkhead_for AS memo,
formatDate(checkhead_checkdate) AS f_checkdate,
formatMoney(checkhead_amount) AS f_amount,
INITCAP(spellAmount(checkhead_amount, curr_id)) AS f_words,
CASE WHEN(checkhead_void) THEN TEXT('V O I D')
ELSE TEXT('')
END AS f_void,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_number
FROM custinfo
WHERE (cust_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_code
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'V' THEN (SELECT vend_number
FROM vendinfo
WHERE (vend_id=checkhead_recip_id))
ELSE 'Unknown Recipient Type'
END AS recip_number,
formatAddr(CASE WHEN checkhead_recip_type = 'C' THEN
(SELECT cntct_addr_id
FROM cntct, custinfo
WHERE ((cust_cntct_id=cntct_id)
AND (cust_id=checkhead_recip_id)))
WHEN checkhead_recip_type = 'T' THEN
(SELECT taxauth_addr_id
FROM taxauth
WHERE (taxauth_id=checkhead_recip_id))
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_addr_id
FROM vendaddrinfo
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_addr_id
FROM vendinfo
WHERE (vend_id=checkhead_recip_id)))
END) AS check_address,
CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_name
FROM custinfo
WHERE cust_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_name
FROM taxauth
WHERE taxauth_id=checkhead_recip_id)
WHEN checkhead_recip_type = 'V' THEN
COALESCE((SELECT vendaddr_name
FROM vendaddr
WHERE ((UPPER(vendaddr_code)='REMIT')
AND (vendaddr_vend_id=checkhead_recip_id))),
(SELECT vend_name
FROM vendinfo
WHERE (vend_id=checkhead_recip_id)))
END AS recip_name,
curr_symbol, curr_abbr, curr_name
FROM vend, checkhead, curr_symbol
WHERE ((checkhead_curr_id = curr_id)
AND (checkhead_id=<? value("checkhead_id") ?>) );

Add a LIMIT 1 at the end of your query, and let me know if this helped.

**************************
FROM vend, checkhead, curr_symbol
WHERE ((checkhead_curr_id = curr_id)
AND (checkhead_id=<? value("checkhead_id") ?>) )
LIMIT 1;

We are now down to one check again, but the account number is wrong. I believe the account number it is displaying is that of the first vendor in the database.

HERE IS THE FX FOR YOUR SQL:

****************************

FROM checkhead, curr_symbol, vend
WHERE ( (checkhead_recip_id = vend_id)
AND (checkhead_curr_id = curr_id)
AND (checkhead_id=<? value("checkhead_id") ?>) )
LIMIT 1;