Copy SQL to VB code not working

  • Thread starter Thread starter potsy via AccessMonster.com
  • Start date Start date
P

potsy via AccessMonster.com

I am trying to use DoCmd.RunSQL with the following...

INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, Beach_Field, Who,
[Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, Prc, Yld, Edte, Udte, Fl )
SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")) AS New_Sec_Field, tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " &
Round(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy") AS
Beach_Field, tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn,
tbl_BEACH_AGY.Mat, tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp,
tbl_BEACH_AGY.Sprd, tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld,
tbl_BEACH_AGY.Edate, tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
FROM tbl_BEACH_AGY
GROUP BY (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")), tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy"),
tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn, tbl_BEACH_AGY.Mat,
tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp, tbl_BEACH_AGY.Sprd,
tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld, tbl_BEACH_AGY.Edate,
tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
HAVING (((Count((IIf(IsNull([tbl_BEACH_AGY]![CUSIP]),[tbl_BEACH_AGY]![Desc] &
" " & Round([tbl_BEACH_AGY]![Cpn],3) & " " & [tbl_BEACH_AGY]![Mat] & " Corp",
[tbl_BEACH_AGY]![CUSIP] & " Corp"))))>=1));


I''ve copied the above SQL from a query and tried to paste it into a VB
module but the code changes the font color to red. Obviously, because there
is an error. Sorry but I am not sure how to covert this SQL to work in VB.
Any suggestions? Would someone "please" be kind enough to write the correct
code so I can copy it into VB module? I know I am asking a lot but I cant
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
 
Sorry, I don't have time to rewrite it, but you can do a couple of things.

One, but I suppose you've already considered this, is for you to just
leave the SQL in a named Query and invoke that Query from VBA.

Two, you can't just paste this SQL -- it's not a valide VBA statement,
expecially since it occupies several lines. You'll probably have to
express it as a quoted string, and split it among several lines. I
sometimes do that by copying the text to Notepad and linking the lines
by deleting the CR at the end of each line (using the Del key).

Once it's in one long line, enclose it in quotation marks and double up
the quotation marks inside the string, so that

... & " " & Round ...

becomes

... & "" "" & Round ...

.. Then, at convenient places, you can insert

" & _
"

just about anywhere except inside those double "" marks. Thus you could
convert

"INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, ..."

into

"INSERT INTO tbl_BEACH_ALL " & _
"( Security_Name_Import, CUSIP, ..."

You could test this after you finish by trying to display it using
MsgBox. If what's displayed looks just like your original SQL, then
you've probably done it correctly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


I am trying to use DoCmd.RunSQL with the following...

INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, Beach_Field, Who,
[Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, Prc, Yld, Edte, Udte, Fl )
SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")) AS New_Sec_Field, tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " &
Round(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy") AS
Beach_Field, tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn,
tbl_BEACH_AGY.Mat, tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp,
tbl_BEACH_AGY.Sprd, tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld,
tbl_BEACH_AGY.Edate, tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
FROM tbl_BEACH_AGY
GROUP BY (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")), tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy"),
tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn, tbl_BEACH_AGY.Mat,
tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp, tbl_BEACH_AGY.Sprd,
tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld, tbl_BEACH_AGY.Edate,
tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
HAVING (((Count((IIf(IsNull([tbl_BEACH_AGY]![CUSIP]),[tbl_BEACH_AGY]![Desc] &
" " & Round([tbl_BEACH_AGY]![Cpn],3) & " " & [tbl_BEACH_AGY]![Mat] & " Corp",
[tbl_BEACH_AGY]![CUSIP] & " Corp"))))>=1));


I''ve copied the above SQL from a query and tried to paste it into a VB
module but the code changes the font color to red. Obviously, because there
is an error. Sorry but I am not sure how to covert this SQL to work in VB.
Any suggestions? Would someone "please" be kind enough to write the correct
code so I can copy it into VB module? I know I am asking a lot but I cant
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
 
You will be suprised how simple this is. It worked for me..
Add a single " before the first word INSERT and make sure all your SQL code
is on ONE line. It should be fine then.

HTH
 
It's because you have quotes inside the SQL.

You need to either replace the double quotes inside the SQL with single
quotes, or double up every double quote (other than the ones at the start
and end of the string)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


potsy via AccessMonster.com said:
I am trying to use DoCmd.RunSQL with the following...

INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, Beach_Field, Who,
[Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, Prc, Yld, Edte, Udte, Fl )
SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")) AS New_Sec_Field, tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " &
Round(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy") AS
Beach_Field, tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn,
tbl_BEACH_AGY.Mat, tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp,
tbl_BEACH_AGY.Sprd, tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld,
tbl_BEACH_AGY.Edate, tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
FROM tbl_BEACH_AGY
GROUP BY (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")), tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy"),
tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn, tbl_BEACH_AGY.Mat,
tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp, tbl_BEACH_AGY.Sprd,
tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld, tbl_BEACH_AGY.Edate,
tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
HAVING
Click to expand...
(((Count((IIf(IsNull([tbl_BEACH_AGY]![CUSIP]),[tbl_BEACH_AGY]![Desc] &
" " & Round([tbl_BEACH_AGY]![Cpn],3) & " " & [tbl_BEACH_AGY]![Mat] & " Corp",
[tbl_BEACH_AGY]![CUSIP] & " Corp"))))>=1));


I''ve copied the above SQL from a query and tried to paste it into a VB
module but the code changes the font color to red. Obviously, because there
is an error. Sorry but I am not sure how to covert this SQL to work in VB.
Any suggestions? Would someone "please" be kind enough to write the correct
code so I can copy it into VB module? I know I am asking a lot but I cant
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
 
You have had some pretty good answers; however, if you are not that familiar
with VBA, there are a couple of things you will need to know. First, since
this line is so long, you will want to use the concatenation character for
combining multiple text strings into one. That is the & character. It works
like this.
strOne = "ABC"
strTwo = "EFG"
strThree = strOne & strTwo
now strThree will be "ABCEFG"

Next, you will need the line continuation character. That is used in VBA to
allow a single line of code to span multiple lines. This is the _ character.
You will see it in the example below. This is very useful for keeping all
your code narrow enough you don't have to scroll horizontally to read your
code.

strSQL = "INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, " _
& " Beach_Field, Who, [Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, " _
& "Prc, Yld, Edte, Udte, Fl ) " _
& "SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc _
& ' ' & Round(tbl_BEACH_AGY!Cpn,3) & ' ' & tbl_BEACH_AGY!Mat _
& ' Corp', tbl_BEACH_AGY!CUSIP 'And So ON

Then, I would recommend using the Execute method of CurrentDb. It is much
faster than the RunSql because it bypasses the Access UI and goes directly to
Jet.

CurrentDb.Execute(strSQL), dbFailOnError

You need the dbFailOnError because if there is an error and you don't use
it, you will not know an error occured because it is not processed by the
Access UI.


potsy via AccessMonster.com said:
I am trying to use DoCmd.RunSQL with the following...

INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, Beach_Field, Who,
[Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, Prc, Yld, Edte, Udte, Fl )
SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")) AS New_Sec_Field, tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " &
Round(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy") AS
Beach_Field, tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn,
tbl_BEACH_AGY.Mat, tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp,
tbl_BEACH_AGY.Sprd, tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld,
tbl_BEACH_AGY.Edate, tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
FROM tbl_BEACH_AGY
GROUP BY (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & tbl_BEACH_AGY!Mat & " Corp",tbl_BEACH_AGY!CUSIP
& " Corp")), tbl_BEACH_AGY.CUSIP, tbl_BEACH_AGY!Desc & " " & Round
(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy"),
tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn, tbl_BEACH_AGY.Mat,
tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp, tbl_BEACH_AGY.Sprd,
tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld, tbl_BEACH_AGY.Edate,
tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
HAVING (((Count((IIf(IsNull([tbl_BEACH_AGY]![CUSIP]),[tbl_BEACH_AGY]![Desc] &
" " & Round([tbl_BEACH_AGY]![Cpn],3) & " " & [tbl_BEACH_AGY]![Mat] & " Corp",
[tbl_BEACH_AGY]![CUSIP] & " Corp"))))>=1));


I''ve copied the above SQL from a query and tried to paste it into a VB
module but the code changes the font color to red. Obviously, because there
is an error. Sorry but I am not sure how to covert this SQL to work in VB.
Any suggestions? Would someone "please" be kind enough to write the correct
code so I can copy it into VB module? I know I am asking a lot but I cant
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
 
Thank you so much! It worked on the first try...
You have had some pretty good answers; however, if you are not that familiar
with VBA, there are a couple of things you will need to know. First, since
this line is so long, you will want to use the concatenation character for
combining multiple text strings into one. That is the & character. It works
like this.
strOne = "ABC"
strTwo = "EFG"
strThree = strOne & strTwo
now strThree will be "ABCEFG"

Next, you will need the line continuation character. That is used in VBA to
allow a single line of code to span multiple lines. This is the _ character.
You will see it in the example below. This is very useful for keeping all
your code narrow enough you don't have to scroll horizontally to read your
code.

strSQL = "INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, " _
& " Beach_Field, Who, [Desc], Cpn, Mat, Call, Amt, Tp, Sprd, Tsy, " _
& "Prc, Yld, Edte, Udte, Fl ) " _
& "SELECT (IIf(IsNull(tbl_BEACH_AGY!CUSIP),tbl_BEACH_AGY!Desc _
& ' ' & Round(tbl_BEACH_AGY!Cpn,3) & ' ' & tbl_BEACH_AGY!Mat _
& ' Corp', tbl_BEACH_AGY!CUSIP 'And So ON

Then, I would recommend using the Execute method of CurrentDb. It is much
faster than the RunSql because it bypasses the Access UI and goes directly to
Jet.

CurrentDb.Execute(strSQL), dbFailOnError

You need the dbFailOnError because if there is an error and you don't use
it, you will not know an error occured because it is not processed by the
Access UI.
I am trying to use DoCmd.RunSQL with the following...
Click to expand...
[quoted text clipped - 28 lines]
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
Click to expand...
 
Thank you. I am on the right travk now.
It's because you have quotes inside the SQL.

You need to either replace the double quotes inside the SQL with single
quotes, or double up every double quote (other than the ones at the start
and end of the string)
I am trying to use DoCmd.RunSQL with the following...
Click to expand...
[quoted text clipped - 14 lines]
(tbl_BEACH_AGY!Cpn,3) & " " & Format(tbl_BEACH_AGY!Mat,"mm/dd/yyyy"),
tbl_BEACH_AGY.Who, tbl_BEACH_AGY.Desc, tbl_BEACH_AGY.Cpn, tbl_BEACH_AGY.Mat,
tbl_BEACH_AGY.Call, tbl_BEACH_AGY.Amt, tbl_BEACH_AGY.Tp, tbl_BEACH_AGY.Sprd,
tbl_BEACH_AGY.Tsy, tbl_BEACH_AGY.Prc, tbl_BEACH_AGY.Yld, tbl_BEACH_AGY.Edate,
tbl_BEACH_AGY.Udate, tbl_BEACH_AGY.Fl
HAVING (((Count((IIf(IsNull([tbl_BEACH_AGY]![CUSIP]),[tbl_BEACH_AGY]![Desc] &
Click to expand...
[quoted text clipped - 8 lines]
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
Click to expand...
 
Thanks to you and a few other replies I got it to work. thank you.

Vincent said:
Sorry, I don't have time to rewrite it, but you can do a couple of things.

One, but I suppose you've already considered this, is for you to just
leave the SQL in a named Query and invoke that Query from VBA.

Two, you can't just paste this SQL -- it's not a valide VBA statement,
expecially since it occupies several lines. You'll probably have to
express it as a quoted string, and split it among several lines. I
sometimes do that by copying the text to Notepad and linking the lines
by deleting the CR at the end of each line (using the Del key).

Once it's in one long line, enclose it in quotation marks and double up
the quotation marks inside the string, so that

... & " " & Round ...

becomes

... & "" "" & Round ...

. Then, at convenient places, you can insert

" & _
"

just about anywhere except inside those double "" marks. Thus you could
convert

"INSERT INTO tbl_BEACH_ALL ( Security_Name_Import, CUSIP, ..."

into

"INSERT INTO tbl_BEACH_ALL " & _
"( Security_Name_Import, CUSIP, ..."

You could test this after you finish by trying to display it using
MsgBox. If what's displayed looks just like your original SQL, then
you've probably done it correctly.

I am trying to use DoCmd.RunSQL with the following...
Click to expand...
[quoted text clipped - 28 lines]
find the error. I've concluded it has something to do with a lines of code
in the module and/or maybe the "IIf" statement???
Click to expand...
Click to expand...
 
Back
Top