SQL statement works in Access and not in VB 2

[Denaeghel]

This SQL-statement works in ACCESS 2000 and not in VB 6.0? What's wrong with it?

txtZoek.Text = "*" & txtZoek.Text & "*"

With rsZoekAuteurs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open "Select Bestanden.Bestandsnummer, & _
Bestanden.Bestandsnaam, Bestanden.Soort, & _
Bestanden.Omschrijving, Bestanden.Auteurs, & _
Bestanden.[Datum Archivering] from Bestanden & _
where Bestanden.Auteurs like '" & txtZoek.Text & "'"
End With
Set Adodc1.Recordset = rsZoekAuteurs

 

[petermeachem]

what sort of doesnt work. what happens Peter Meachem
peter@accuflight.com

 

[Denaeghel]

A datagrid is connected to adodc1 and it doesn't show us the result. The strange thing is that when we replace "like" by "=" in the SQL-statement the thing works!?!

 

[petermeachem]

instead of

'" & txtZoek.Text & "'"

use

'" & txtZoek.Text & "*'"

or

'*" & txtZoek.Text & "'"

or even

'*" & txtZoek.Text & "*'"


Peter Meachem
peter@accuflight.com

 

[Denaeghel]

Sorry, it doesn't work. I just mistyped my string. It is the same result.

 

[MisterC]

Have you tried using the % and _ SQL wildcards?

 

[rtshort]

.Open "Select Bestanden.Bestandsnummer, & _
Bestanden.Bestandsnaam, Bestanden.Soort, & _
Bestanden.Omschrijving, Bestanden.Auteurs, & _
Bestanden.[Datum Archivering] from Bestanden & _
where Bestanden.Auteurs like '%" & txtZoek.Text & "%'"

Try it this way and see if it will work. I think it should.
Every day above ground is a GOOD DAY!!!

 

[McClane]

i think you should write it this way....

SQL = "Select Bestanden.Bestandsnummer, " & _
"Bestanden.Bestandsnaam, Bestanden.Soort, " & _
"Bestanden.Omschrijving, Bestanden.Auteurs, " & _
"Bestanden.[Datum Archivering] from Bestanden " & _
"where Bestanden.Auteurs like '" & txtZoek.Text & "'"