Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by Johnny48

  1. Johnny48

    Exdcel VBA to Verify Pricing

    Thanks DjangMan. We import via CSV files, I use VBA/Excel and yes with convoluted logic to test for the various pricing combos...as our company has «fairly» simple costing I let VBA handle it...even sends HTML email to admins on every scanned batch ! The reason being: 1- EDI software we use...
  2. Johnny48

    Exdcel VBA to Verify Pricing

    Still testing...but the below seems to do it !: SELECT ICPRICP.UNITPRICE, ICPRICP.DPRICETYPE, ICPRICP.PRICELIST, ICPRICP.SALESTART, ICPRICP.SALEEND, ICPRIC.PRICESTART, ICPRIC.PRICEEND, ARCSP.IDCUST, ARCSP.IDCUSTSHPT, ICPRICP.ITEMNO, ICCUPR.EXPIRE, ICCUPR.FIXPRICE, ICCUPR.STARTDATE...
  3. Johnny48

    Exdcel VBA to Verify Pricing

    Hello, I need to verify pricing before importing sales orders into accpac 6.0/MSSQL I have the vba I need, however having trouble getting the correct SQL query to return rows with information needed by VBA procedure to verify pricing. Fields needed to verify pricing: ICPRICP.UNITPRICE...
  4. Johnny48

    VBA to Create Shipment ACCPAC 5.6A

    Yes, but I want to use the above code form Excel...
  5. Johnny48

    VBA to Create Shipment ACCPAC 5.6A

    I found some code and modified a bit... Sub MainSub() OESHI1detail5.Compose Array(OESHI1header) OESHI1header.Cancel OESHI1header.Init OESHI1headerFields("ORDNUMBER").Value = " & sheet1.cells(1,1)& " ' Order Number to generate SHIPMENT and INOICE for... for i = 1 to LastRow...
  6. Johnny48

    VBA to Create Shipment ACCPAC 5.6A

    Hello, I need a «Quick» way for our shipping dept to create a SHIPMENT once an order was picked.... I was thinking of this: Dump this query onto an Excel spreadsheet: SELECT OEORDH.ORDNUMBER, OEORDH.ORDUNIQ, OEORDD.LINETYPE, OEORDD.ITEM, OEORDD.DESC, OEORDD.QTYBACKORD FROM OEORDD INNER JOIN...
  7. Johnny48

    UPDaTE JOINED TABLES

    Thank You Mirtheil :), This seem to do the trick without killing the tables...: UPDATE POPORL SET POPORL.EXPARRIVAL = 20100418 FROM POPORH1 WHERE POPORL.PORHSEQ=POPORH1.PORHSEQ AND POPORH1.PONUMBER='004386'
  8. Johnny48

    UPDaTE JOINED TABLES

    Hello.. Will this work : UPDATE POPORL SET POPORL.EXPARRIVAL = 20100418 FROM POPORH1 POPORL WHERE POPORL.PORHSEQ=POPORH1.PORHSEQ AND POPORH1.PONUMBER="004386"
  9. Johnny48

    Web Query

    Hello, We are using Sage Accpac 500 ERP (Version 5.5A). Is it possible to have web pages QUERY the database ? What set ups must be done if possible to accomplish this ?
  10. Johnny48

    SELECTING 2 ROWS AS ONE

    Thanks Mirtheil, I needed to add a bracket after the AND in the WHERE clause.... Now I retreive 2 rows with the below, one row with the value column heading of VALLUE containing the value for ICITEMO.OPTFIELD="4250 and another row with the value ICITEMO.OPTFIELD)="4110. Can this be returned...
  11. Johnny48

    SELECTING 2 ROWS AS ONE

    Thanks Mirtheil, However does not seem return the «both» values as columns for the ICITEMO.VALUES..... SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED...
  12. Johnny48

    SELECTING 2 ROWS AS ONE

    Hello, Trying to built a simple ACCPAC report which requires OPTIONAL FIELD TABLE VALUES to be JOINED to the MAIN select ...... I have this : SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA...
  13. Johnny48

    INVENTORY QTY + VALUE + FIRST COST

    Hi, Not sure how to answer this..... One query would ne nice ...but several is fine as I will run through excel... 1- SELECT ICHIST.ITEMNO, ICITEM.DESC, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO =...
  14. Johnny48

    INVENTORY QTY + VALUE + FIRST COST

    Again, thanks.. Now I have this: SELECT ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE, ICHIST.HOMEEXTCST/ICHIST.QUANTITY AS POCOST, Max(ICHIST.TRANSDATE) AS MaxOfTRANSDATE FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO = ICITEM.ITEMNO GROUP BY ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE...
  15. Johnny48

    INVENTORY QTY + VALUE + FIRST COST

    *lol* I guess I should pay more attention to the data dictionary.... Thansk again etienne...Got this now: SELECT ICHIST.ITEMNO, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST FROM ICHIST WHERE (((ICHIST.ITEMNO)="101614") AND ((ICHIST.TRANSDATE)<=20100115))...
See more

Part and Inventory Search

Back
Top