Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

SQL "Select"

VB6, access95 database.
Existing code: Form1.Data1.RecordSource = "Select*From Table Where Jones like '" & Form1.Text1.Text & "*'"

This works fine. My problem...Can I refer to a field name in a listbox, replacing the field name (Jones) above.

In other words, what would be the proper syntax for "Select*From Table Where form1.list1.text like '" & Form1.Text1.Text & "*'"
I hope I'm making myself clear.
0
jtallsup
Asked:
jtallsup
  • 9
  • 7
  • 6
  • +3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Use % instead of * like this:
"Select * From Table Where Jones like '" & Form1.Text1.Text & "%'"
0
 
jtallsupAuthor Commented:
Guess I didn't make myself clear. I need to avoid using the field name (Jones), and select the field name itself from a listbox.
0
 
MarineCommented:
data1.recordsourse="SELECT * FROM table1 where '" & list1.Text & "' & " Like " & '" & text1.Text & "'"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MarineCommented:
data1.recordSource = "SELECT * FROM table1 where '" & List1.Text & "' Like '" & text1.Text & "'"
0
 
Éric MoreauSenior .Net ConsultantCommented:
Instead, use this one:
"Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & Form1.Text1.Text & "%'"
0
 
MarineCommented:
Emoreau, what do you mean instead use ?  My suggestion is valid as well if the values in the listbox are string. yours are valid for numeric. So both of our answers are correct.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Marine,

I mean: Instead of MY previous answer!!! Reading your comment again, it can't work since the Listbox control doesn't have a Text property!
0
 
MarineCommented:
sorry you right. i just copied list.text from his example i would use list1.listindex then for my example just replacing list.text with list.listindex.
0
 
jtallsupAuthor Commented:
Adjusted points to 127
0
 
jtallsupAuthor Commented:
Marine & emoreau:
Here's the last of my points for putting up with my ignorance. I'm sure we're on the right track, but I may be reading something wrong. VB insists on reading any single quote as the beginning of a comment. also, would combo1.text be proper?
0
 
MarineCommented:
if its a combo then yes. Single quotes are needed to enclose the string values , data values inclosed into ## signs numeric data are left alone. Hope that explains.
0
 
MarineCommented:
I meant date/time values are enclosed into  # # .
REM if you using singlq quote outsite someplace then ofcourse its a comment.
0
 
alokanantCommented:
Hi itallsup,
You may face problem in case Form1.Text1.Text includes single quotes.
In that case you have to use a function to convert all the single quotes in Form1.Text1.Text or combo1.text to "'".
Is this the problem?

hth
alok.
0
 
alokanantCommented:
if that's the case here is a function to convert Form1.Text1.Text to a suitable format -

Public Function Apostrophe(sFieldString As String) As String

              If InStr(sFieldString, "'") Then
                     Dim iLen As Integer
                     Dim ii As Integer
                     Dim apostr As Integer
                     iLen = Len(sFieldString)
                     ii = 1

                            Do While ii <= iLen
                                    If Mid(sFieldString, ii, 1) = "'" Then
                                    apostr = ii
                                   sFieldString = Left(sFieldString, apostr) & "'" & _
                                   Right(sFieldString, iLen - apostr)
                                    iLen = Len(sFieldString)
                                    ii = ii + 1
                                    End If
                                    ii = ii + 1
                            Loop

              End If

       strClean = sFieldString
        'strClean is a public string used to save the newly formatted string
End Function

The new string shall be
"Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & Apostrophe(Form1.Text1.Text) & "%'"


hth
alok
0
 
nutwissCommented:
it may be worth storing the field types as an enumerated integer stored in the listbox's itemdata() array

then you can simply use a Select...Case, or Switch() to apply the correct formatting to the query

0
 
Éric MoreauSenior .Net ConsultantCommented:
«VB insists on reading any single quote as the beginning of a comment»

You probably have a mismatch in your double quotes. Your complete line should read like this:
Form1.Data1.RecordSource = "Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & Form1.Text1.Text & "%'"
0
 
jtallsupAuthor Commented:
Thanks everyone, but no luck.  Marine & emoreau; none of these returned any records. alokanant; not the problem, thanks anyway. nutwiss; that's over this beginner's head. I found a work-around that won't be as neat as I was hoping, but better than my original code. Thanks for the attempts, but I guess I'll give this one up.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I hope that you refresh your recordset after changing the recordsource (ie. form1.data1.refresh).

Can you show you code where you put this query. It has to work. It should work for a long time now!
0
 
Éric MoreauSenior .Net ConsultantCommented:
Also, are you sure that your query is valid?

Try something for me:
-After setting the recordsource property, output it to the Immediate Window;
-Copy the query and paste it to Access to run it. If Access can't execute it, VB won't.

To output it to the Immediate window, you can use this:
Form1.Data1.RecordSource = "Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & Form1.Text1.Text & "%'"
Debug.Print Form1.Data1.RecordSource
stop

You can view the Immediate window by pressing CTRL-G.
0
 
babeleCommented:
You can also use this routine:

DoubleApostrophe(Form1.Text1.Text)


Public Function DoubleApostrophe(ByVal sBr As String) As String

Dim lPos As Long
Dim sBl As String

If Len(sBr) = 0 Then Exit Function
lPos = InStr(sBr, Chr$(39))
While lPos <> 0
   sBl = sBl & Left$(sBr, lPos) & Chr$(39)
   sBr = Right$(sBr, Len(sBr) - lPos)
   lPos = InStr(sBr, Chr$(39))
Wend
DoubleApostrophe = sBl & sBr
End Function


0
 
Éric MoreauSenior .Net ConsultantCommented:
babele,

Since jtallsup is using VB6, it would be a lot easier to use the replace function like this:
Form1.Data1.RecordSource = "Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & replace(Form1.Text1.Text,"'","''") & "%'"
0
 
jtallsupAuthor Commented:
This is precisely the situation;
My original code works perfectly:

form2.data1.recordsource = "Select*from KData where Kendall like '" & form1.text1.text & "*'"

form2.Data1.Refresh

if form2.Data1.Recordset.Recordcount > 0 Then
Form1.Label1.Caption = ""
form2.show
end if

if form2.Data1.Recordset.Recordcount = 0 Then
Form1.Label1.Caption = "No data."
end if

When using this:
 form2.Data1.RecordSource = "Select * From KData Where " & Form1.Combo1.Text & " like '" & Form1.Text1.Text & "%'"

no records are returned, ("no data" label). When using the debug.print suggestion, the immediate window shows:
Select * From KData Where Kendall like '8881513132%'

The field name (Kendall) is read properly, but the field contents should be simply 8881513132 rather than '8881513132%'

Also, the * substituted for the % gives the same results.
0
 
Éric MoreauSenior .Net ConsultantCommented:
If your value is numeric, you can't use the LIKE operator and you don't surround it with quotes!

Your query should be this one:
form2.data1.recordsource = "Select*from KData where Kendall = " & form1.text1.text
0
 
jtallsupAuthor Commented:
The value sometimes contains letters, ie 88815TZ036. What then?
0
 
Éric MoreauSenior .Net ConsultantCommented:
Then you should keep single quotes (supposing that the Kendall field is character).

 form2.Data1.RecordSource = "Select * From KData Where " & Form1.Combo1.Text & " like '" & Form1.Text1.Text & "*'"
0
 
jtallsupAuthor Commented:
That did it! Thanks a bunch, it will definitely make this thing one hell of a lot cleaner. Thanks to the others as well, sorry I don't have more points to spread around.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now