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

[Guest]

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

 

[Baz]

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

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

 

[Guest]

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

I found that I had misused quotes and parenthesis.

Thanks anyway.

 

[John Nurick]

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)';

 

[Guest]

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,

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