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 |
#1
|
|||
|
|||
Learning SQL .... Help Please
The following SQL compiles and runs as expected.
strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," strSQL = strSQL & " OldDiscount )" strSQL = strSQL & " SELECT Date()as ChngeDate, tblMaterialMaster.SISItemCode," strSQL = strSQL & " tblMaterialMaster.Discount" strSQL = strSQL & " FROM tblMaterialMaster" strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" Now when I try to introduce a stand alone variable which was established thru an input box I get error code 3061 "too few parameters" Expected 1 Dim sglDiscount as Single strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," strSQL = strSQL & " OldDiscount, NewDiscount)" strSQL = strSQL & " SELECT Date()as ChngeDate, tblMaterialMaster.SISItemCode," strSQL = strSQL & " tblMaterialMaster.Discount, 'sglDiscount' as NewDiscount" strSQL = strSQL & " FROM tblMaterialMaster" strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" The above compiles and steps thru the entire code with no error but does NOT update NewDiscount. It must be in the syntax. Any thoughts please? |
#2
|
|||
|
|||
Learning SQL .... Help Please
The SQL you've written is attempt to write the string "sqlDiscount' to your
history table. Is that what you want, or are you actually trying to write the value contained in a variable named sglDiscount? If it's the variable you want, use strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," strSQL = strSQL & " OldDiscount, NewDiscount)" strSQL = strSQL & " SELECT Date(), tblMaterialMaster.SISItemCode," strSQL = strSQL & " tblMaterialMaster.Discount, " & sglDiscount strSQL = strSQL & " FROM tblMaterialMaster" strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" Note that unless strItem contains a wildcard character, there's no reason to use Like (use = instead). Note, too, that I removed the aliases "ChngeDate" and "NewDiscount" from your subselect: they serve no useful purpose. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "TeeSee" wrote in message ... The following SQL compiles and runs as expected. strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," strSQL = strSQL & " OldDiscount )" strSQL = strSQL & " SELECT Date()as ChngeDate, tblMaterialMaster.SISItemCode," strSQL = strSQL & " tblMaterialMaster.Discount" strSQL = strSQL & " FROM tblMaterialMaster" strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" Now when I try to introduce a stand alone variable which was established thru an input box I get error code 3061 "too few parameters" Expected 1 Dim sglDiscount as Single strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," strSQL = strSQL & " OldDiscount, NewDiscount)" strSQL = strSQL & " SELECT Date()as ChngeDate, tblMaterialMaster.SISItemCode," strSQL = strSQL & " tblMaterialMaster.Discount, 'sglDiscount' as NewDiscount" strSQL = strSQL & " FROM tblMaterialMaster" strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" The above compiles and steps thru the entire code with no error but does NOT update NewDiscount. It must be in the syntax. Any thoughts please? |
#3
|
|||
|
|||
Learning SQL .... Help Please
On Jan 19, 3:11*pm, "Douglas J. Steele"
wrote: The SQL you've written is attempt to write the string "sqlDiscount' to your history table. Is that what you want, or are you actually trying to write the value contained in a variable named sglDiscount? If it's the variable you want, use strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," * * strSQL = strSQL & " OldDiscount, NewDiscount)" * * strSQL = strSQL & " SELECT *Date(), tblMaterialMaster.SISItemCode," * * strSQL = strSQL & " tblMaterialMaster.Discount, " & sglDiscount * * strSQL = strSQL & " FROM tblMaterialMaster" * * strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" Note that unless strItem contains a wildcard character, there's no reason to use Like (use = instead). Note, too, that I removed the aliases "ChngeDate" and "NewDiscount" from your subselect: they serve no useful purpose. -- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele (no e-mails, please!) "TeeSee" wrote in message ... The following SQL compiles and runs as expected. strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," * *strSQL = strSQL & " OldDiscount )" * *strSQL = strSQL & " SELECT *Date()as ChngeDate, tblMaterialMaster.SISItemCode," * *strSQL = strSQL & " tblMaterialMaster.Discount" * *strSQL = strSQL & " FROM tblMaterialMaster" * *strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" Now when I try to introduce a stand alone variable which was established thru an input box I get error code 3061 "too few parameters" Expected 1 Dim sglDiscount as Single strSQL = "INSERT INTO tblMaterialMasterHistory ( ChngeDate, SISitemCode," * *strSQL = strSQL & " OldDiscount, NewDiscount)" * *strSQL = strSQL & " SELECT *Date()as ChngeDate, tblMaterialMaster.SISItemCode," * *strSQL = strSQL & " tblMaterialMaster.Discount, 'sglDiscount' as NewDiscount" * *strSQL = strSQL & " FROM tblMaterialMaster" * *strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like " & """" & strItem & """)" The above compiles and steps thru the entire code with no error but does NOT update NewDiscount. It must be in the syntax. Any thoughts please?- Hide quoted text - - Show quoted text - Thanks Doug ... That did the trick. Just a couple of comments .... my strItem does and most often will have wildcards in it and I found the Date() as ChngeDate in the SQL test within a blank query grid SQL. Thanks for bringing those to my attention. With regards to the fields in the Append query is it imperative that the order of the fields in the INSERT line is identical to the order of the incoming data on the SELECT line? |
#4
|
|||
|
|||
Learning SQL .... Help Please
On Mon, 19 Jan 2009 15:59:11 -0800 (PST), TeeSee
wrote: With regards to the fields in the Append query is it imperative that the order of the fields in the INSERT line is identical to the order of the incoming data on the SELECT line? Absolutely. That's the only way that Access can tell which value to put into which field. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|