Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL "Select"

Posted on 2000-02-28
26
Medium Priority
?
293 Views
Last Modified: 2013-12-25
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
Comment
Question by:jtallsup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 6
  • +3
26 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2566933
Use % instead of * like this:
"Select * From Table Where Jones like '" & Form1.Text1.Text & "%'"
0
 

Author Comment

by:jtallsup
ID: 2566970
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
 
LVL 6

Expert Comment

by:Marine
ID: 2567036
data1.recordsourse="SELECT * FROM table1 where '" & list1.Text & "' & " Like " & '" & text1.Text & "'"
0
Independent Software Vendors: 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!

 
LVL 6

Expert Comment

by:Marine
ID: 2567045
data1.recordSource = "SELECT * FROM table1 where '" & List1.Text & "' Like '" & text1.Text & "'"
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2567065
Instead, use this one:
"Select * From Table Where " & form1.List1.list(form1.list1.ListIndex) & " like '" & Form1.Text1.Text & "%'"
0
 
LVL 6

Expert Comment

by:Marine
ID: 2567169
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2567217
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
 
LVL 6

Expert Comment

by:Marine
ID: 2567265
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
 

Author Comment

by:jtallsup
ID: 2567273
Adjusted points to 127
0
 

Author Comment

by:jtallsup
ID: 2567274
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
 
LVL 6

Expert Comment

by:Marine
ID: 2567282
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
 
LVL 6

Expert Comment

by:Marine
ID: 2567284
I meant date/time values are enclosed into  # # .
REM if you using singlq quote outsite someplace then ofcourse its a comment.
0
 
LVL 2

Expert Comment

by:alokanant
ID: 2567487
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
 
LVL 2

Expert Comment

by:alokanant
ID: 2567494
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
 
LVL 4

Expert Comment

by:nutwiss
ID: 2568323
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2568611
«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
 

Author Comment

by:jtallsup
ID: 2569124
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2569167
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2569182
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
 

Expert Comment

by:babele
ID: 2569188
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2569224
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
 

Author Comment

by:jtallsup
ID: 2569450
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2569618
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
 

Author Comment

by:jtallsup
ID: 2569629
The value sometimes contains letters, ie 88815TZ036. What then?
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 508 total points
ID: 2569920
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
 

Author Comment

by:jtallsup
ID: 2570205
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question