Strings located in SQL statement

Strings located in SQL statement - how to quote..??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Ehlert (dehlert) - 30 Sep 2005 17:21 GMT

I am working in Access 2003.

I am working on importing a TEXT file and then cleaning it up prior to
extracting the data.

Throughout the file, I have the following records:
*****(2008) and *****(2616)
where the asterisks are actually spaces. The field that this data is
imported to is defined as a text field with a length of 100.

The VB code I wrote looked like the following:

txtTmpData = " (2008)"
sqlstmt = "DELETE tblDNBRawData.RecNo, tblDNBRawData.txtData,
Len([tblDNBRawData].[txtData]) " _
& "FROM tblDNBRawData " _
& "WHERE [tblDNBRawData].[txtData]= " & txtTmpData & ";"
DB.Execute (sqlstmt)

I performed a debug.print of sqlstmt and it shows:

DELETE tblDNBRawData.RecNo, tblDNBRawData.txtData,
Len([tblDNBRawData].[txtData]) FROM tblDNBRawData WHERE
[tblDNBRawData].[txtData]= (2008);

AS you can see, the spaces are there between equal sign and the '(2008)',
but I also get a data type mismatch error.

Could someone help me in fixing this SQL Delete statement..??

Thanks in advance.

David Ehlert
IT Analyst - Communications
County of Fresno

Reply to this Message

Baz - 30 Sep 2005 17:37 GMT

> I am working in Access 2003.
>
[quoted text clipped - 31 lines]
> IT Analyst - Communications
> County of Fresno

sqlstmt = "DELETE tblDNBRawData.RecNo, tblDNBRawData.txtData,
Len([tblDNBRawData].[txtData]) " _
& "FROM tblDNBRawData " _
& "WHERE [tblDNBRawData].[txtData]= """ & txtTmpData & """;"

Reply to this Message

David Ehlert (dehlert) - 30 Sep 2005 17:39 GMT

****** Please ignore this post *******

I found that I had misused quotes and parenthesis.

Thanks anyway.

> I am working in Access 2003.
>
[quoted text clipped - 31 lines]
> IT Analyst - Communications
> County of Fresno

Reply to this Message

John Nurick - 30 Sep 2005 19:36 GMT

>DELETE tblDNBRawData.RecNo, tblDNBRawData.txtData,
>Len([tblDNBRawData].[txtData]) FROM tblDNBRawData WHERE
>[tblDNBRawData].[txtData]= (2008);

David, I see from your later post that you've got the quoting sorted.
But the SQL DELETE works on entire records and there's no point
specifying fields. All you need is

DELETE FROM tblDNBRawData
WHERE txtData=' (2008)';

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

David Ehlert (dehlert) - 30 Sep 2005 20:05 GMT

> >DELETE tblDNBRawData.RecNo, tblDNBRawData.txtData,
> >Len([tblDNBRawData].[txtData]) FROM tblDNBRawData WHERE
[quoted text clipped - 11 lines]
>
> Please respond in the newgroup and not by email.

John,

Thanks for the pointer. I never even thought about doing it that way.
The funny part about your suggestion is that I just found I wrote some code
like that about two years ago. I forgot about it until you mentioned it.

I appreciate the responses.

David Ehlert

Reply to this Message