Solved

SQL "Select"

Posted on 2000-02-28
26
280 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
  • 9
  • 7
  • 6
  • +3
26 Comments
 
LVL 69

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
 
LVL 6

Expert Comment

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

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 69

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 69

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 69

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 69

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 69

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 69

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 69

Accepted Solution

by:
Éric Moreau earned 127 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now