Solved: Query Struggle

[Imdabaum]

Sorry for formatting. I can't get it to word wrap. It just keeps going. But onto my question.
I've loaded this into sqlInv as String


Dim invSQL As String
invSQL = "SELECT TBL_INVOICES.INVOICE_NBR, TBL_CRF.ID AS CRFID, TBL_COMMISSIONS_STRUCTURE.ID AS ComStrucID, " & _
"TBL_INVOICES.GLPOSTDT, TBL_CRF.VendorID, TBL_CRF.Commission_Plan_Type, " & _
"TBL_COMMISSIONS_STRUCTURE.CommissionAmount, TBL_INVOICES.INVOICE_AMT, " & _
"TBL_COMMISSIONS_STRUCTURE.StartDate TBL_COMMISSIONS_STRUCTURE.EndDate , TBL_INVOICES.DATE_PAID_OFF, " & _
"TBL_COMMISSIONS_STRUCTURE.CommissionType " & _
"FROM TBL_INVOICES, TBL_CRF INNER JOIN TBL_COMMISSIONS_STRUCTURE ON TBL_CRF.ID=TBL_COMMISSIONS_STRUCTURE.CRFID " & _
"WHERE (((TBL_INVOICES.INVOICE_NBR) = IIf(TBL_COMMISSIONS_STRUCTURE!CommissionType = " & _
"'Recurring', TBL_CRF!Recur_Invc_NBR, TBL_CRF!Setup_Invc_NBR)) And ((TBL_INVOICES.DATE_PAID_OFF) <= " & _
GetCommissionPayableParam() & " " & _
"ORDER BY TBL_CRF.VendorID, TBL_COMMISSIONS_STRUCTURE.CommissionType DESC;" 'appropriate & _ at each end line.


Can anyone figure out what I am doing wrong? The code errors out with Error 91 if or with block not set, but the real problem is that I've got some syntax problems that I'm not spotting. Sorry for the mundane problem. But I'm :banghead: :banghead: .

Edited 18-Jan-10 by geekgirlau. Reason: insert line breaks

[orange]

I don't know your application, but I noticed there is no comma after StartDate. There my be more but that's the first thing I noticed.

[Imdabaum]

I have a query that returns any invoices that have been paid off. Then I want to run an insert on a table using that query to pay vendors responsible for those invoices.

IE. VendorID = 12 makes a sell. InvoiceNBR=INVC0012345 is created with an amount.
VendorID 12 gets % of InvoiceAmt once the invoice is paid off.

So query returns vendor information, invoiceAmt, and the amount to be paid to the vendor. But the accountant will enter a date parameter. If any results in the query are in the same month of that parameter, then they should be inserted into the vendor_Earned table.

The previous query returns the values I need to insert. But it fails if I run it in VBA, and works fine when I run it directly from the query group.


Essentially the end result would be this query(Which doesn't add any records at the moment despite there being records returning in QRY_IPTC)

INSERT INTO TBL_COMMISSIONS_Earned ( CStructureID, FK_CRFID, VendorID, CommissionEarned, InvoiceNBR, InvoiceAMT, InvoicePostDate, CommissionType )
SELECT QRY_IPTC.ComStrucID, QRY_IPTC.CRFID, QRY_IPTC.VendorID, QRY_IPTC.CommissionAmount, QRY_IPTC.INVOICE_NBR, QRY_IPTC.INVOICE_AMT, QRY_IPTC.GLPOSTDT, QRY_IPTC.CommissionType
FROM TBL_COMMISSIONS_Earned AS CE, QRY_IPTC;

[orange]

I have a query that returns any invoices that have been paid off. Then I want to run an insert on a table using that query to pay vendors responsible for those invoices.

IE. VendorID = 12 makes a sell. InvoiceNBR=INVC0012345 is created with an amount.
VendorID 12 gets % of InvoiceAmt once the invoice is paid off.

So query returns vendor information, invoiceAmt, and the amount to be paid to the vendor. But the accountant will enter a date parameter. If any results in the query are in the same month of that parameter, then they should be inserted into the vendor_Earned table.

The previous query returns the values I need to insert. But it fails if I run it in VBA, and works fine when I run it directly from the query group.


Essentially the end result would be this query(Which doesn't add any records at the moment despite there being records returning in QRY_IPTC)

INSERT INTO TBL_COMMISSIONS_Earned ( CStructureID, FK_CRFID, VendorID, CommissionEarned, InvoiceNBR, InvoiceAMT, InvoicePostDate, CommissionType )
SELECT QRY_IPTC.ComStrucID, QRY_IPTC.CRFID, QRY_IPTC.VendorID, QRY_IPTC.CommissionAmount, QRY_IPTC.INVOICE_NBR, QRY_IPTC.INVOICE_AMT, QRY_IPTC.GLPOSTDT, QRY_IPTC.CommissionType
FROM TBL_COMMISSIONS_Earned AS CE, QRY_IPTC;

How are you trying to run this in vba? Can you show the relevant code?

[Imdabaum]

I had the Append Query saved zQRYappend. Then on the form where the date criteria is entered, I was going to execute the query on button_click.

This didn't seem to work, so now I'm trying it through VBA. I've gotten the original select query working, and am just using .AddNew record if it meets the criteria. Having a new issue with the .FindFirst method.


rs = db.openrecordset(qdef) ' the sql query
rs2 = db.openrecordset(tblDef) ' table to insert data into.

'fails
rs2.FindFirst "ID = " & rs.Fields("ID") & "INVNBR Like " & Trim(rs.Fields("INVCNBR") ) & "*"
' Just tried and it fails too
rs2.FindFirst "ID = " & rs.Fields("ID") & " AND " & "INVNBR Like " & Trim(rs.Fields("INVCNBR")) & "*"


Still chipping away...

Thanks for checking back.

[Imdabaum]

.FindFirst method.


rs = db.openrecordset(qdef) ' the sql query
rs2 = db.openrecordset(tblDef) ' table to insert data into.

rs2.FindFirst "ID = " & rs.Fields("ID") & " AND " & "INVNBR Like '" & _
Trim(rs.Fields("INVCNBR")) & "*'"
'seems to work.:clap: :clap: :clap:


Thanks for your help and
Thanks for checking back.


I'll figure out why the query won't run from the saved query later on I guess.

[geekgirlau]

If you already have a Select query, change the query to an Append query. Test to see if the query runs correctly, then you should be able to use DoCmd.OpenQuery to execute it.