SQL String

[GLT]

Hi,

I have tried to run the following SQL in vba, however it will not execute,
can anyone advise what the problem is?

strSQL = "DELETE [Daily Backup Results].Subject, [Daily Backup
Results].From, " & _
"[Daily Backup Results].Received, [Daily Backup Results].Body " & _
"FROM [Daily Backup Results] " & _
"WHERE ((([Daily Backup Results].Body) Like """"*\Program
Files\*""""));"

 

[Dirk Goldgar]

Hi,

I have tried to run the following SQL in vba, however it will not execute,
can anyone advise what the problem is?

strSQL = "DELETE [Daily Backup Results].Subject, [Daily Backup
Results].From, " & _
"[Daily Backup Results].Received, [Daily Backup Results].Body "
& _
"FROM [Daily Backup Results] " & _
"WHERE ((([Daily Backup Results].Body) Like """"*\Program
Files\*""""));"

In questions like this, it's always helpful to know what kind of error
you're getting. The one obvious thing is that you appear to have too many
quotes surrounding the literal in your Like expression -- quadrupled quotes
instead of doubled quotes. Try this:

"WHERE ((([Daily Backup Results].Body) Like ""*\Program Files\*""));"

Or you could use a single pair of single-quotes instead:

"WHERE ((([Daily Backup Results].Body) Like '*\Program Files\*'));"

 

[GLT]

Hi Dirk,

Thanks for your help - using the single quotes makes it much easier - i will
do it this way in future - thanks for your help ...

Cheers,
GLT

Hi,

I have tried to run the following SQL in vba, however it will not execute,
can anyone advise what the problem is?

strSQL = "DELETE [Daily Backup Results].Subject, [Daily Backup
Results].From, " & _
"[Daily Backup Results].Received, [Daily Backup Results].Body "
& _
"FROM [Daily Backup Results] " & _
"WHERE ((([Daily Backup Results].Body) Like """"*\Program
Files\*""""));"

In questions like this, it's always helpful to know what kind of error
you're getting. The one obvious thing is that you appear to have too many
quotes surrounding the literal in your Like expression -- quadrupled quotes
instead of doubled quotes. Try this:

"WHERE ((([Daily Backup Results].Body) Like ""*\Program Files\*""));"

Or you could use a single pair of single-quotes instead:

"WHERE ((([Daily Backup Results].Body) Like '*\Program Files\*'));"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

Thanks for your help - using the single quotes makes it much easier - i
will
do it this way in future - thanks for your help ...

Just be aware that, if your string literal contains the apostrophe (or
single-quote) character, then you can't use the single-quotes to enclose it.
For example, if you were looking for the name "O'Toole", then you couldn't
write:

strSQL = "SELECT ... WHERE LastName='O'Toole'"

You'd need to use the double-quotes, and double them up because they're
inside the quoted string:

strSQL = "SELECT ... WHERE LastName=""O'Toole"""

But in most cases you can avoid having to do that.