If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
|
#12
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
I guess it's Sunday and everyone's a bit sleepy, but you've been given some
pretty weird suggestions so far. One problem seems blindingly obvious to me: I can't imagine why you'd want to put a line-feed character in the middle of an SQL string. Replacing it with vbCRLF is just as bizarre. Try this: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev FROM CHOOSEData WHERE " & _ & "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" wrote in message ... 2003 The following code works in the Access Query SQL-view window but when I try to run it in VBA, it produces the following error (#3601): "Too few parameters. Expected 1" dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & Chr(10) & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" I copied the Access query which works! exactly from the SQL window (Except I had to parse the lines with &, " etc. I guess the syntax I am using is incorrect after the .Execute command. (Note the . INTO CHOOSERev 10th line Any help greatly appreciated! EagleOne |
#13
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
wrote in message ... On Aug 10, 9:01 am, wrote: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & Chr(10) & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" Sometimes Access gives strange error messages. I would look at the following: --There is no space after " INTO CHOOSERev". --There is a line-feed character before and after "FROM CHOOSEData". This is the nub of the problem. All that is needed is a space before and after. --There is a semi-colon embedded in the sql string. It's the statement terminator, which is fine. You don't have to put one, but it's OK if you do. --There is a limit to the length of an sql statement and you are wasting part of that limited number of characters by repeating "CHOOSEDATA.". ( I think the limit is 255 characters ) No, that limit only applies to an SQL string used as a RecordSource property or RowSource property. |
#14
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
"Michael Gramelspacher" wrote in message ... On Sun, 10 Aug 2008 09:30:25 -0700 (PDT), wrote: try this dbs.Execute _ " a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _ " a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _ " a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _ " a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _ " a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _ " INTO [CHOOSERev] FROM [CHOOSEData]" & _ " WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _ " AND ((a.LTrim_REG) '7'));" How can he execute that string, it isn't even a statement? There's no SELECT... |
#15
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Hi John,
The line feed character shouldn't be there at all (a linefeed in the middle of an SQL statement???), adding a carriage return to it isn't going to help! "John Spencer" wrote in message ... Try replacing Chr(10) with VbCRLF or with Chr(13) & Chr(10) dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & vbcrlf & "FROM CHOOSEData" & vbcrlf _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === |
#16
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
On Sun, 10 Aug 2008 18:44:52 +0100, "bcap" wrote:
"Michael Gramelspacher" wrote in message .. . On Sun, 10 Aug 2008 09:30:25 -0700 (PDT), wrote: try this dbs.Execute _ " a.AAA_UIC, a.ACRN, a.AMT, a.DOC_NUMBER, a.FIPC," & _ " a.REG_NUMB, a.TRAN_TYPE, a.DOV_NUM, a.PAA," & _ " a.COST_CODE, a.OBJ_CODE, a.EFY, a.REG_MO, a.RPT_MO," & _ " a.EFFEC_DATE, a.Orig_Sort, a.LTrim_BFY, a.LTrim_AAA," & _ " a.LTrim_REG, a.LTrim_DOV, a.AMT_Rev, a.CONCACT" & _ " INTO [CHOOSERev] FROM [CHOOSEData]" & _ " WHERE (((a.TRAN_TYPE) IN ('1K','2D'))" & _ " AND ((a.LTrim_REG) '7'));" How can he execute that string, it isn't even a statement? There's no SELECT... Yes, of course. Just a clerical error on my part, but not hard to correct now that the error has been pointed out. |
#17
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
On Aug 10, 10:47*am, "bcap" wrote:
Hi John, The line feed character shouldn't be there at all (a linefeed in the middle of an SQL statement???), adding a carriage return to it isn't going to help! "John Spencer" wrote in message ... Try replacing Chr(10) with VbCRLF or with * *Chr(13) & Chr(10) dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ *& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ *& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ *& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ *& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ *& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ *& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ *& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " *& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ *& " INTO CHOOSERev" _ *& vbcrlf & "FROM CHOOSEData" & vbcrlf _ *& " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" '================================================= === *John Spencer *Access MVP 2002-2005, 2007-2008 *The Hilltop Institute *University of Maryland Baltimore County '================================================= ===- Hide quoted text - - Show quoted text - bcap: Sorry if I misled the group. The limitation on the length of a string comes from the limitation on continuation characters. Sub Main() Dim sqlStmt As String sqlStmt = String(80, "A") & _ String(80, "B") & _ String(80, "C") & _ String(80, "D") & _ String(80, "E") & _ String(80, "F") & _ String(80, "G") & _ String(80, "H") & _ String(80, "B") & _ String(80, "I") & _ String(80, "J") & _ String(80, "K") & _ String(80, "L") & _ String(80, "M") & _ String(80, "N") & _ String(80, "O") & _ String(80, "P") & _ String(80, "Q") & _ String(80, "R") & _ String(80, "S") & _ String(80, "T") & _ String(80, "U") & _ String(80, "V") & _ String(80, "W") MsgBox (sqlStmt) End Sub Try to add an "X" string and an error will occur. My recollection about the allowable length of an SqlStatement was faulty. |
#18
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
The Chr(10) line-feed characters serve no purpose here. Just use a space
character. As you are only returning columns from one table there is no need to qualify the column names with the table names, so: Dim dbs as DAO.Database Dim strSQL As String Set dbs = CurrentDb strSQL = "SELECT " & _ " BFY, APPN_SYMB, SBHD, BCN, SA_SX," & _ " AAA_UIC, ACRN, AMT, DOC_NUMBER, FIPC," & _ " REG_NUMB, TRAN_TYPE, DOV_NUM, PAA," & _ " COST_CODE, OBJ_CODE, EFY, REG_MO, RPT_MO," & _ " EFFEC_DATE, Orig_Sort, LTrim_BFY, LTrim_AAA," & _ " LTrim_REG, LTrim_DOV, AMT_Rev, CONCACT" & _ " INTO CHOOSERev FROM CHOOSEData" & _ " WHERE TRAN_TYPE IN (""1K"",""2D"")" & _ " AND LTrim_REG ""7""" dbs.Execute strSQL I'm assuming 'CONCACT' is not a typo. Ken Sheridan Stafford, England " wrote: 2003 The following code works in the Access Query SQL-view window but when I try to run it in VBA, it produces the following error (#3601): "Too few parameters. Expected 1" dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev" _ & Chr(10) & "FROM CHOOSEData" & Chr(10) _ & " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" I copied the Access query which works! exactly from the SQL window (Except I had to parse the lines with &, " etc. I guess the syntax I am using is incorrect after the .Execute command. (Note the . INTO CHOOSERev 10th line Any help greatly appreciated! EagleOne |
#19
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Same error! error (#3601): Too few parameters. Expected 1"
Trust me I am, at least, just as frustrated. What I believe is that the issue is so obvious that we are all walking right past it. Folks, please do not give up! **************** Please Continue Below *************************************** The VBA module from which this VBA is invoked, is in "Tools.mdb" While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb" In Recon.mdb, I have previously created a table "ChooseData" I want to use Recon.mdb's "ChooseData" table to create a new table called "ChooseRev" in Recon.mdb !!!! PROGRESS !!!! NOTE: The following seems to create something (it does not bomb!) Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a solution? If it produces CHOOSERev, a "RecordSet" then what command can I use to save it in Recon.mdb (alias dbs and not in Tools.mdb?) EagleOne "bcap" wrote: dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _ & "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC, " _ & "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _ & "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _ & "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _ & "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _ & "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _ & "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _ & "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _ & " INTO CHOOSERev FROM CHOOSEData WHERE " & _ & "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'));" |
#20
|
|||
|
|||
SQL code works in Access SQL window but not in VBA SQL code
Ken, thanks for weighing in. I still get the same error message.
Please read my last posting (previous) above. I am concerned that the error message is incorrect as to the real reason. I am beginning to feel that attempting to establish a new table in project 2 from a table currently in table 2) from project 1's VBA module may be an issue that is masking the real error. Also, look at my comment about the successful SET (RecordSet) command. I am relatively new to VBA SQL Access. Therefore, if I have a RecordSet "CHOOSERev" from a Project "Recon.mdb" table "CHOOSEData", then how do I save the RecordSet "CHOOSERev" into Project "Recon.mdb" table "CHOOSERev"? (Again I am executing this code from Project Tools.mdb.) Ken Sheridan wrote: strSQL = "SELECT " & _ " BFY, APPN_SYMB, SBHD, BCN, SA_SX," & _ " AAA_UIC, ACRN, AMT, DOC_NUMBER, FIPC," & _ " REG_NUMB, TRAN_TYPE, DOV_NUM, PAA," & _ " COST_CODE, OBJ_CODE, EFY, REG_MO, RPT_MO," & _ " EFFEC_DATE, Orig_Sort, LTrim_BFY, LTrim_AAA," & _ " LTrim_REG, LTrim_DOV, AMT_Rev, CONCACT" & _ " INTO CHOOSERev FROM CHOOSEData" & _ " WHERE TRAN_TYPE IN (""1K"",""2D"")" & _ " AND LTrim_REG ""7""" dbs.Execute strSQL |
Thread Tools | |
Display Modes | |
|
|