Copy SQL to VB code not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

potsy - 29 Mar 2006 06:09 GMT

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

Reply to this Message

Vincent Johns - 29 Mar 2006 07:07 GMT

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 <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> I am trying to use DoCmd.RunSQL with the following...
>
[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???

Reply to this Message

potsy - 01 Apr 2006 19:12 GMT

Thanks to you and a few other replies I got it to work. thank you.

>Sorry, I don't have time to rewrite it, but you can do a couple of things.
>
[quoted text clipped - 43 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???

Reply to this Message

ashg657 - 29 Mar 2006 08:26 GMT

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

Signature

Ash
Don''t forget to rate as helpful if I have helped you,Thanks!
admin@ukliquid.co.uk

Reply to this Message

Douglas J Steele - 29 Mar 2006 13:41 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I am trying to use DoCmd.RunSQL with the following...
>
[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] &
[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???

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200603/1

Reply to this Message

potsy - 01 Apr 2006 19:10 GMT

Thank you. I am on the right travk now.

>It's because you have quotes inside the SQL.
>
[quoted text clipped - 19 lines]
>
>http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200603/1

Reply to this Message

Klatuu - 29 Mar 2006 15:50 GMT

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.

Reply to this Message

potsy - 01 Apr 2006 19:09 GMT

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
[quoted text clipped - 34 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???

Reply to this Message