Sql Like

I get an error when i try to execute the SQL query below. Anyone know what to do?

Dim selectedwave as String
selectedwave = "'" & waveList.List(waveList.ListIndex) & "%'"

strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul '00' WHERE wave LIKE " & selectedwave & ""

I get an error when i try to execute the SQL query below. Anyone know what to do?

Dim selectedwave as String
selectedwave = "'" & waveList.List(waveList.ListIndex) & "%'"

strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul '00' WHERE wave LIKE " & selectedwave & ""

The problem is that this string: "1) Jun - Jul '00" that contains a ' before the year. Does anyone know how to type the query correctly?

Hello.

I posted this reply in another thread. As I have not heard from the user yet, I do not know if it worked. Anyway, you can try using the escape key before the quote. See if this works:

strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul \'00' WHERE wave LIKE " & selectedwave & ""

Hope it helps.

Thanks for the reply but it did not work. Got the "missing operator in query" error. Any other idea?


Hello.

I posted this reply in another thread. As I have not heard from the user yet, I do not know if it worked. Anyway, you can try using the escape key before the quote. See if this works:

strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul \'00' WHERE wave LIKE " & selectedwave & ""

Hope it helps.

Sorry bout that. Try replacing the escape key (\) with the single quote (') key to indicate one of them is not part of the end of the quote, but instead part of the string:

Dim strSQL1 as String
'Add another single quote before '00
strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul ''00' WHERE wave LIKE '" & selectedwave & "'"


Hope this works :)

You are quick :) thanks but i did not work this time either. I got a 'data type mismatch' error. Ready to give another advice?


Sorry bout that. Try replacing the escape key (\) with the single quote (') key to indicate one of them is not part of the end of the quote, but instead part of the string:

Dim strSQL1 as String
'Add another single quote before '00
strSQL1 = "UPDATE Table1 SET wave = '1) Jun - Jul ''00' WHERE wave LIKE '" & selectedwave & "'"


Hope this works :)

Strange....

As far as SQL is concerned, when the single quote is to be part of the string, you should be able to circumvent the problem by adding another single quote. Could you paste a little more of your code, just in case the fault might be elsewhere? Thanks.

I think you are right, there is sometthing else that is the problem. Iam trying another query like this:

Dim hej As String
hej = "1255A" <--this gives me a 'missing operator in query expression'
'hej = "1255" <-- this is working

strSQL1 = "UPDATE Table1 SET wave = " & hej & " WHERE wave LIKE '1%'"

It seems like it wont accept any letters in the String. I looked in the database and the field was a TEXT field so it should not be any problem.
Can you figure it out?

Strange....

As far as SQL is concerned, when the single quote is to be part of the string, you should be able to circumvent the problem by adding another single quote. Could you paste a little more of your code, just in case the fault might be elsewhere? Thanks.

You should enclose the " & hej & " string within a pair of single quotes. Try this:

'Single quote, double quote, space, ampersand, space, hej, space, ampersand, space, double quote, single quote.
strSQL1 = "UPDATE Table1 SET wave = '" & hej & "' WHERE wave LIKE '1%'"

Pray it works ;)

did not work.
I know what the problem is...
My program does this:
Copying a excel sheet into a temporary access table called Table1.
The program updates Table1 and copy the right values into another table and after that deletes Table1.

The problem is that the excel sheet with the column named "wave" only contains numbers and when i copy the excel sheet into Table1(being created when the program runs) it atomaticly makes the "wave" column in Table1 to a NUMBER field. So even if my "wave" column in the table that i keep to the end is a text TEXT field i need also change the "wave" column in Table1 to a TEXT field while the program is running. Is there anyway to do that?

thanks again for all the help!!


You should enclose the " & hej & " string within a pair of single quotes. Try this:

'Single quote, double quote, space, ampersand, space, hej, space, ampersand, space, double quote, single quote.
strSQL1 = "UPDATE Table1 SET wave = '" & hej & "' WHERE wave LIKE '1%'"

Pray it works ;)

Can you let me know if the database is an Access database or SQL database? Cause if it is Access, my limited knowledge ends here. If SQL, I might be able to help out abit. Cheers.

To bad..it's Access!
But thanks for all the help!

Can you let me know if the database is an Access database or SQL database? Cause if it is Access, my limited knowledge ends here. If SQL, I might be able to help out abit. Cheers.

You need two single quotes before and after, plus another to finish enclosing the string"UPDATE Table1 SET wave = '1) Jun - Jul ''00''' WHERE wave LIKE '" & selectedwave & "'"And your LIKE is pointless, since you don't give it a wildcard it will function just like = only slower.