ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> Using ODBC to print Open PO's
http://www.istechforum.com/YaBB.pl?num=1439907533

Message started by IT Department on 08/18/15 at 07:18:53

Title: Using ODBC to print Open PO's
Post by IT Department on 08/18/15 at 07:18:53

Hi,

Was hoping if someone could help me out here. I'd like to create an ODBC query in EXCEL to display a list of Open Purchase Orders over a specified date range (2 weeks out, etc). I've used ODBC before, but I'm not sure which table has the necessary info, for example I tried BKPOX and BKPOXH. Ideally I'd like to have the same info that's generated when we use the PO > I > G program in DBA.

I understand that the program already displays the date but it has been requested to have that list display within Excel to save time formatting, etc.

Thanks

Title: Re: Using ODBC to print Open PO's
Post by Kelloggs on 08/18/15 at 07:20:58

Just open PO's? or PO Lines as well?

You can use this as a reference point:

SELECT
BKAPPOL.BKAP_POL_PONM,
BKAPPO.BKAP_PO_VNDNME,
BKAPPO.BKAP_PO_ENTBY,
BKAPPOL.NKAP_POL_UM_LIN_1,
BKAPPOL.BKAP_POL_ERD,
BKAPPOL.BKAP_POL_PCODE,
BKAPPOL.BKAP_POL_PDESC,
BKAPPOL.BKAP_POL_PQTY,
BKAPPOL.BKAP_POL_RQTY,
BKAPPOL.BKAP_POL_ARD,
BKAPPOL.BKAP_POL_WOPRE,
BKAPPOL.BKAP_POL_WOSUF
FROM BKAPPO INNER JOIN BKAPPOL ON BKAPPO.BKAP_PO_NUM = BKAPPOL.BKAP_POL_PONM
WHERE LENGTH(BKAP_POL_PCODE) <> 0 AND (BKAPPOL.BKAP_POL_PQTY - BKAPPOL.BKAP_POL_RQTY) <> 0
AND BKAPPOL.BKAP_POL_PQTY <> 0
AND BKAP_POL_ERD >= '2015-07-01' AND BKAP_POL_ERD <= '2015-07-31'
ORDER BY BKAP_POL_PONM ASC

If you remove the "BKAPPOL" columns you will have to use the "BKAPPO.BKAP_PO_ORDDTE" for your date range
and the "BKAPPO.BKAP_PO_PRTD" to select only open. I think is <>'R'

Have fun,

;)

Kelloggs

Title: Re: Using ODBC to print Open PO's
Post by Kelloggs on 08/18/15 at 07:36:06

Me again,

the LENGTH(BKAP_POL_PCODE) <> 0

should be

ASCII(LTRIM(RTRIM(BKAP_POL_PCODE))) <> 0

:-[

Kelloggs

Title: Re: Using ODBC to print Open PO's
Post by IT Department on 08/18/15 at 11:56:57

Thanks Kelloggs!

I'll play with that and see where things go. Right now, they are only asking for Open Purchase Orders.

Title: Re: Using ODBC to print Open PO's
Post by Kelloggs on 08/18/15 at 12:10:54

Actually there is not such a thing as an "Open or Close" Purchase Order.
It is Fully Received, Partially Received, or Un-received.

In reality you should use the Purchase Order Lines to determinate what PO is "Open" or "Close"
of course you don't have to "show" the purchase order line fields.

Here is your Excel VBA Code :

Private Sub get_data()
Dim odbc_su As String
Dim su_conn As New ADODB.Connection
Dim su_rs As New ADODB.Recordset
Dim curr_wb As Workbook
Dim curr_sh As Worksheet
Dim row_01 As Integer
Dim var_sql As String

odbc_su = "DBA" 'THIS IS YOUR ODBC CONNECTION

Set curr_wb = ThisWorkbook
Set curr_sh = curr_wb.Sheets("Main") ' YOUR SHEET NAME

su_conn.CursorLocation = adUseClient
su_conn.Open odbc_su

var_sql = "HERE YOU ENTER YOUR SQL SCRIPT"
su_rs.Open var_sql, su_conn, adOpenStatic
curr_sh.Cells("A5 P500").Value = Empty 'CLEAN PREVIOUS VALUES it is missing the : the forum shows it as an smiley face
row_01 = 5
Do While Not su_rs.EOF
curr_sh.Cells(row_01, 1).Value = su_rs("MY FIELD")
curr_sh.Cells(row_01, 2).Value = su_rs("MY OTHER FIELD")
' MORE ROWS HERE ........
su_rs.MoveNext
row_01 = row_01 + 1
Loop
su_rs.Close
su_conn.Close

End Sub

Have fun !!!

;D

Kelloggs

Title: Re: Using ODBC to print Open PO's
Post by IT Department on 08/18/15 at 13:13:29

Interesting. Thanks once again Kelloggs!