really dumb and easy question

  • Thread starter Thread starter AngiW
  • Start date Start date
A

AngiW

I've done this a million times and i can't remember how i did it. I had kids
and they took my brain cells!!!

How do i wrap text in vba?

For example:
docmd. runsql "UPDATE blah blah blah
SET blah blah blah
WHERE blah blah blah";

I keep getting an expected end of statment error and I know you need the _ but
i'm missing something else. Thanks!
 
AngiW said:
I've done this a million times and i can't remember how i did it. I
had kids and they took my brain cells!!!
Click to expand...

They'll do it every time.
How do i wrap text in vba?

For example:
docmd. runsql "UPDATE blah blah blah
SET blah blah blah
WHERE blah blah blah";

I keep getting an expected end of statment error and I know you need
the _ but i'm missing something else. Thanks!
Click to expand...

Try:

Docmd. RunSQL "UPDATE blah blah blah " & _
"SET blah blah blah " & _
"WHERE blah blah blah;"

Note that, in addition to breaking the string into three concatenated
pieces with continuations between, I've also move the semicolon back
inside the string where it belongs.
 
Dirk,
I knew the ampersand was in there...i kept forgetting the space between it and
the _.

Now I have a new problem:
Run time error '3067':
Query input must contain at least one table or query.

I run this through a query and it runs fine...put it in code...get this error?
Anyone know why? I'll repost this under a new one if i need to.

DoCmd.RunSQL "INSERT INTO [Accrual Main] ( employeeID, SickAvail, updmonthsick
)" & _
"SELECT [Employee Main].[Employee ID], 5 AS Expr1, Month(date()) AS
Expr2" & _
"FROM [Employee Main]" & _
"WHERE ((([Employee Main].Status)='Full time') AND (([Employee
Main].[Mod Comp])=No));"
 
AngiW said:
Dirk,
I knew the ampersand was in there...i kept forgetting the space
between it and the _.

Now I have a new problem:
Run time error '3067':
Query input must contain at least one table or query.

I run this through a query and it runs fine...put it in code...get
this error? Anyone know why? I'll repost this under a new one if i
need to.

DoCmd.RunSQL "INSERT INTO [Accrual Main] ( employeeID, SickAvail,
updmonthsick )" & _
"SELECT [Employee Main].[Employee ID], 5 AS Expr1,
Month(date()) AS Expr2" & _
"FROM [Employee Main]" & _
"WHERE ((([Employee Main].Status)='Full time') AND (([Employee
Main].[Mod Comp])=No));"
Click to expand...

Probably you've just confused the SQL parser by not having a space
between elements of the statement. If you look at the SQL statement
you've built, you'll see it looks like this:

INSERT INTO [Accrual Main] ( employeeID, SickAvail,
updmonthsick)SELECT [Employee Main].[Employee ID], 5 AS Expr1,
Month(date()) AS Expr2FROM [Employee Main]WHERE ((([Employee
Main].Status)='Full time') AND (([Employee Main].[Mod Comp])=No));

You should add blanks inside the string between "updmonthsick)" and
"SELECT", between "Expr2" and "FROM", and between "[Employee Main]" and
"WHERE". It's most likely the second of these errors that is giving
rise to the error you're getting, but you should fix them all.
 
I've done this a million times and i can't remember how i did it. I had kids
and they took my brain cells!!!

How do i wrap text in vba?

For example:
docmd. runsql "UPDATE blah blah blah
SET blah blah blah
WHERE blah blah blah";

I keep getting an expected end of statment error and I know you need the _ but
i'm missing something else. Thanks!
Click to expand...

A blank before the underscore, probably.

I would suggest NOT trying to wrap a single string constant across
lines - I find it easier to use concatenation, e.g.

docmd. runsql "UPDATE blah blah blah" _
& " SET blah blah blah" _
& " WHERE blah blah blah";

Be sure to leave the syntactically required blanks in the string (i.e.
" SET rather than "SET ).
 
Back
Top