I missed that! The alias cust should be cm. I shortened it trying to correct the error. I also noticed I missed custCustomerID. Once I changed that to cm.CustomerID, the query ran! I had just been looking at it too long yesterday and needed another set of eyes.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, December 11, 2015 4:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] OpenRecordset Method Name Argument String Breaks At 1024 Characters
Darrell-
I tried this:
Sub TestSQL()
Dim strSQL_Select As String
Dim strSQL_From As String
Dim strSQL_Where As String
Dim strSQL As String
strSQL_Select = "SELECT cm.[Number], cm.[Group No], custCustomerID, cm.[Account Name], cm.[Preferred MultiGroup No], cm.[GEAC Group Code], "
strSQL_Select = strSQL_Select & "cm.[Ledger No], cm.[Retention Ledger No], cm.[Bill Cycle], cm.[Quarterly Settlement], cm.[Self Billed], "
strSQL_Select = strSQL_Select & "cm.Cancelled, cm.[Excluded from Weekly], cm.MultiGroup, cm.[Excluded from Payments], cm.[ACH Groups], "
strSQL_Select = strSQL_Select & "cm.[Contact First Name], cm.[Contact Last Name], cm.[Business Segment], cm.[Payment Frequency], cm.eBookshelfPosting, "
strSQL_Select = strSQL_Select & "cm.BankDescription, cm.BankField, grp.GroupNoConv, grp.InvoiceBalance, grp.IsNationalGroup, grp.RiskCell, grp.AcctAdmin, "
strSQL_Select = strSQL_Select & "grp.MinPremium, grp.Underwriter, grp.RetentionRep, grp.TerritoryCd, grp.OfficeCd , grp.OverallExempt, grp.InvoicePeriod, "
strSQL_Select = strSQL_Select & "grp.InvoiceDt, grp.SalesCd, grp.Comments, grp.BillingAddress1, grp.BillingAddress2, grp.BillingAddress3, grp.BiliingCity, grp.BillingSt, "
strSQL_Select = strSQL_Select & "grp.BillingZip, grp.AddressType, grp.LateFeeGrace, grp.RetentionRepEmail, grp.UnderwriterEmail, grp.RetentionMgrEmail, "
strSQL_Select = strSQL_Select & " grp.GroupNo, grp.GroupCode, grp.GroupName "
strSQL_From = "FROM tblCustomersMain As cust LEFT JOIN tblGroupMain As grp ON cm.[Group No] = grp.GroupNo "
strSQL_Where = "WHERE cm.[Number] = " & 1 & ";"
strSQL = strSQL_Select & strSQL_From & strSQL_Where
Debug.Print strSQL
End Sub
I got:
SELECT cm.[Number], cm.[Group No], custCustomerID, cm.[Account Name], cm.[Preferred MultiGroup No], cm.[GEAC Group Code], cm.[Ledger No], cm.[Retention Ledger No], cm.[Bill Cycle], cm.[Quarterly Settlement], cm.[Self Billed], cm.Cancelled, cm.[Excluded from Weekly], cm.MultiGroup, cm.[Excluded from Payments], cm.[ACH Groups], cm.[Contact First Name], cm.[Contact Last Name], cm.[Business Segment], cm.[Payment Frequency], cm.eBookshelfPosting, cm.BankDescription, cm.BankField, grp.GroupNoConv, grp.InvoiceBalance, grp.IsNationalGroup, grp.RiskCell, grp.AcctAdmin, grp.MinPremium, grp.Underwriter, grp.RetentionRep, grp.TerritoryCd, grp.OfficeCd , grp.OverallExempt, grp.InvoicePeriod, grp.InvoiceDt, grp.SalesCd, grp.Comments, grp.BillingAddress1, grp.BillingAddress2, grp.BillingAddress3, grp.BiliingCity, grp.BillingSt, grp.BillingZip, grp.AddressType, grp.LateFeeGrace, grp.RetentionRepEmail, grp.UnderwriterEmail, grp.RetentionMgrEmail, grp.GroupNo, grp.GroupCode, grp.GroupName FROM tblCustomersMain As cust LEFT J
OIN tblGroupMain As grp ON cm.[Group No] = grp.GroupNo WHERE cm.[Number] = 1;
.. all as one long string, but I do notice the funky line break on LEFT J .. OIN.
However, I don't think that's the issue because you haven't defined "cm" in your FROM clause. You defined grp and cust, but I see no cm.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Dec 11, 2015, at 10:06 PM, 'Embrey, Darrell' DEmbrey@bcbsm.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Sure,
My variables:
Dim strSQL_Select As String
Dim strSQL_From As String
Dim strSQL_Where As String
My string construction:
strSQL_Select = "SELECT cm.[Number], cm.[Group No], custCustomerID, cm.[Account Name], cm.[Preferred MultiGroup No], cm.[GEAC Group Code], "
strSQL_Select = strSQL_Select & "cm.[Ledger No], cm.[Retention Ledger No], cm.[Bill Cycle], cm.[Quarterly Settlement], cm.[Self Billed], "
strSQL_Select = strSQL_Select & "cm.Cancelled, cm.[Excluded from Weekly], cm.MultiGroup, cm.[Excluded from Payments], cm.[ACH Groups], "
strSQL_Select = strSQL_Select & "cm.[Contact First Name], cm.[Contact Last Name], cm.[Business Segment], cm.[Payment Frequency], cm.eBookshelfPosting, "
strSQL_Select = strSQL_Select & "cm.BankDescription, cm.BankField, grp.GroupNoConv, grp.InvoiceBalance, grp.IsNationalGroup, grp.RiskCell, grp.AcctAdmin, "
strSQL_Select = strSQL_Select & "grp.MinPremium, grp.Underwriter, grp.RetentionRep, grp.TerritoryCd, grp.OfficeCd , grp.OverallExempt, grp.InvoicePeriod, "
strSQL_Select = strSQL_Select & "grp.InvoiceDt, grp.SalesCd, grp.Comments, grp.BillingAddress1, grp.BillingAddress2, grp.BillingAddress3, grp.BiliingCity, grp.BillingSt, "
strSQL_Select = strSQL_Select & "grp.BillingZip, grp.AddressType, grp.LateFeeGrace, grp.RetentionRepEmail, grp.UnderwriterEmail, grp.RetentionMgrEmail, "
strSQL_Select = strSQL_Select & " grp.GroupNo, grp.GroupCode, grp.GroupName "
strSQL_From = "FROM tblCustomersMain As cust LEFT JOIN tblGroupMain As grp ON cm.[Group No] = grp.GroupNo "
strSQL_Where = "WHERE cm.[Number] = " & Me.cmbGroupNo.Value & ";"
My OpenRecordset method:
Set rs = db.OpenRecordset(strSQL_Select & strSQL_From & strSQL_Where, dbOpenDynaset)
The Debug.Print for the concatenated string displays two lines in the immediate window. The first line breaks after the "O" in the word JOIN. The second line starts with the "I", contains the remainder of the from clause and the where clause.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, December 11, 2015 3:51 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] OpenRecordset Method Name Argument String Breaks At 1024 Characters
Darrell,
Can you share your code and/or the SQL statement? Have you tried using Debug.Print to capture and display your SQL statement? (Hint: most of us veteran coders do this consistently).
I always use line continuation to keep all of my code visible without scrolling.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 11 Dec 2015 12:42:31 -0800
Subject: [MS_AccessPros] OpenRecordset Method Name Argument String Breaks At 1024 Characters
Greetings All,
I'm trying to perform an OpenRecordset method using a string variable for the SQL. The procedure throws an error for Invalid procedure call or argument. When I view the generated string, it is broken into two lines. The first line contains 1024 characters and breaks in the middle of the word JOIN. The total length of the SQL string is 1100 characters.
I've Tried breaking the SQL into three strings for the Select, From, and Where clauses and then concatenating the three variables for the OpenRecordset Name argument. The result is the same.
Also, I have an error handler in the procedure but, I still get the error dialog box instead of my custom error MsgBox. Yes, I checked the options to ensure the Error Trapping option was not set to Break on All Errors. It is set to Break on Unhadled Errors.
Has anyone experie nced this behavior?
Darrell
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar