Help with find record 1

[lizzieborden]

Having problems finding records in my access database. I can add but no find. I want to be able to put in a word contained in some part of the Description field and be able to find a record. Part of the code works, if I put nothing in then I get error "Insufficient Text".

Dim sql As String
sql = "select * from Issues "
sql += " Where Description = '" & txtfind.Text & "' "
daIssues.SelectCommand.CommandText = sql
DsIssues1.Clear()
daIssues.Fill(DsIssues1)
If txtfind.Text = Nothing Then
MessageBox.Show("Insufficient Text")
'show all records
sql = "select * from Issues "
daIssues.SelectCommand.CommandText = sql
DsIssues1.Clear()
daIssues.Fill(DsIssues1)
End If
End Sub

 

[lizzieborden]

Still not searching? Can anyone suggest?

 

[johnwm]

What sorts of control are DsIssues1 and daIssues?

To concatenate strings in VB, always use the '&' character, as using '+' will lead to odd results if both arguments can be interpreted as numeric. Try changing your line:

[tt]sql += " Where Description = '" & txtfind.Text & "' "[/tt]

to read:[tt]

sql = sql & " Where Description LIKE '%" & txtfind.Text & "%' "
[/tt]

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[lizzieborden]

I tried the sql = sql & " Where Description LIKE '%" & txtfind.Text & "%' " but I'm not getting any results. With the old code I get Insufficient Text whether or not the field is left blank. the dsIssues1 is my dataset and the daIssues is my data adapter. Also, I have tried changing the description "text" field in access to act as the primary field still text.

 

[lizzieborden]

Okay now it works but when I hit next of previous the records are no longer in order... They acted as should before changing key fields. Also, I must say that the id field is an autogenerated number. When I did a find on that field it worked if my autogenerated was changed to text but then the records were out of sequence.

 

[johnwm]

Check out the 'Order By' clause in JetSQL help. Befault location is "C:\Program Files\Common Files\Microsoft Shared\Office10\1033\jetsql40.chm"

Suggest you also check out faq222-2244 to help you give the info needed for us to answer your questions better

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[lizzieborden]

Thanks for the help. Your suggestion (sql = sql & " Where Description LIKE '%" & txtfind.Text & "%' ")did work! It was my database that didn't work. I've been writing code for a new app for the last three weeks. Saved all unknowns for last. Deadline this monday. Probably be on the forum all weekend.