Learning SQL .... Help Please

[TeeSee]

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?

 

[Douglas J. Steele]

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.

 

[TeeSee]

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 reasonto
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!)










- 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?

 

[John W. Vinson]

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.