?
Solved

Stuck on searching a desire word...

Posted on 2003-03-13
15
Medium Priority
?
237 Views
Last Modified: 2010-05-01
Dear all,

I m making a project and i have tried several method to solve it... well my problem is that: i am using VB6 with Access and i am retrieving from the db some info from the tables that i have created. so far, so good! the problem of that is that one column of my table can store info in text form such as "You are a desirable woman". On the code i have made i didnt have any troubles with the connection to access. Do you know when i retrieve info from the db what will be the code for the input box assuming that i want from the software to find me the all the results that have the word "desirable"?????

Thank you in advance
0
Comment
Question by:ls3ag
[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
15 Comments
 
LVL 2

Expert Comment

by:Sweat
ID: 8130551
ls3ag,

You might build a SQL statement like this:

SELECT * from YourTable where SomeField like '*desirable*'

This would return a record set of all rows that have the word 'desirable' within the field called SomeField.  The "*"'s surrounding the word desirable are wildcards.

Let me know if this helps,

Sweat

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8130603
IF you are using DAO to connect to the database, then

SELECT * from YourTable where SomeField like '*desirable*'

is corect.

If you are using ADO, then it should read:

SELECT * from YourTable where SomeField like '%desirable%'


ADO uses the % as the wildcard, DAO uses the *

AW
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8130610
If it does not help, try using % as a wildcard instead.

SELECT * from YourTable where SomeField like '%desirable%'

Dabas
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!

 

Author Comment

by:ls3ag
ID: 8130677
Dear Sweat,

For the rest of my filterings that i have done i was writing the following statement:
 SELECT * FROM Xtable WHERE Xtable.Notam_cat = '" & UCase(strAnswer) & "'")
it was displaying my results in a new form where i didnt have any problems at all AS THE RESULTS WERE JUST A SINGLE VALUE OR WORD.
 now the thing is that i want to search my database if it has one word such as "desirable" and display to me the whole result (" you are a desirable woman")
Do you recommend that this is the answer?

Regards
0
 
LVL 2

Expert Comment

by:Sweat
ID: 8130814
ls3ag,

This sql statement:

SELECT * FROM Xtable WHERE Xtable.Notam_cat = '" & UCase(strAnswer) & "'")

would look for an exact match (see the '=' sign) in the field .Notam_cat rather than a portion of the field.

Using the "like" operator, you are asking the sql statement to search within a particular field for a match to the value submitted, strAnswer.

So.  The building the sql statement should look something like this:

     Dim sSQL$

     sSQL = "SELECT * FROM Xtable WHERE "
     sSQL = sSQL & "Xtable.Notam_cat LIKE '*"
     sSQL = sSQL & strAnswer
     sSQL = sSQL & "*'"

(I split the statement to show it better)

would search within the table column and find all rows with the value of strAnswer in it.

As stated by others, this would be a statement for DAO, for ADO use the percent sign as the wildcard.

Sweat

0
 

Author Comment

by:ls3ag
ID: 8131927
Thank you very much for your information...
I was just thinking if you know what code i need to write in order to place a Print button in my form and let me print out the desirable form....
????????

Thank you in advance
0
 
LVL 2

Expert Comment

by:Sweat
ID: 8132682
ls3ag,

Should I assume that you've already put together code in your drop down menu to do printing?

If so, you would need to place a Command button on the form, size it and label it and perhaps call it btnPrint.

Then under the button you'd just call the drop down menu and then the code under the drop down would execute and print what you need.

Private Sub btnPrint()
    mnuPrint_MyForm
End Sub

Assuming that your print drop down is called mnuPrint_MyForm

Hope this helps, let me know.

Sweat


0
 

Author Comment

by:ls3ag
ID: 8161859
thnx sweat for your help... i have just solved it!!! i really appreciate your help! Now, i am facing something else: i have the code needed for adding something on the db of my project and i need the reverse of that cause it gives the option the software to delete some tuples from the db. i have tried to write the opposite but is not working... the code is mentioned below in which i have used for the add command! is it possiple to help me out for the delete command?

Private Sub cmdCancel_Click()
frmAddCountryCode.Hide
End Sub
**********************************************************
Private Sub cmdOK_Click()
'declaration of response and isok
Dim response As String
Dim isok As Boolean
isok = True
'this is validation to see if a country name has been entered
If isok = True And Len(txtCountryName.Text) = 0 Then
    isok = False
    response = MsgBox("You did not enter a country name!", vbExclamation)
    Beep
End If
'this is validation to see if a country code has been entered
If isok = True And Len(txtaddcountrycode.Text) = 0 Then
    isok = False
    response = MsgBox("You did not enter a country code!", vbExclamation)
    Beep
End If
'this is validation to check if the country code is the right length.
If isok = True And (Len(txtaddcountrycode.Text) > 2 Or Len(txtaddcountrycode.Text) < 2) Then
    isok = False
    response = MsgBox("The code you entered was not 2 letters long!", vbExclamation)
    Beep
End If
'if the validation was successful then the user is asked if what they entered is correct
'otherwise dispalys a message on the form to tell user to try again.
If isok = True Then
    txtCountryName = UCase(txtCountryName.Text)
    txtaddcountrycode.Text = UCase(txtaddcountrycode.Text)
    response = MsgBox("You entered the country code: " & txtaddcountrycode.Text & " and the country name: " & txtCountryName.Text & ". Is this information correct?", vbYesNo + vbQuestion)
Else
    tryagain.Caption = "You did not fill in the form correctly. Please ammend your input and try again"
End If
'if they click no then the same message is displayed
If response = vbNo Then
    txtaddcountrycode.Text = ""
    tryagain.Caption = "You did not fill in the form correctly. Please ammend your input and try again"
End If

'if they click yes it checks to see if either the code or the name are already in the database
If response = vbYes Then
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim CanAdd As Boolean
                                       
    CanAdd = False
           
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open "C:\My Documents\Copy of proto1.mdb"

    Set rs = New ADODB.Recordset
    rs.Open ("SELECT * FROM Countries WHERE Countries.country_code = '" & txtaddcountrycode.Text & "' OR Countries.country_name = '" & txtCountryName.Text & "'"), conn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        CanAdd = False
    Else
        CanAdd = True
    End If
           
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
'if the data is not already there it can be added
'a connection is made and an SQL insert statement is sent to the database to add the new country
    If CanAdd = True Then
       
        Set conn = New ADODB.Connection
        conn.Provider = "Microsoft.Jet.OLEDB.4.0"
        conn.Mode = adModeReadWrite
        conn.Open "C:\My Documents\Copy of proto1.mdb"
       
        Dim SQLInsert As String
        Dim SQLInsert2 As String
       

        SQLInsert = "( '" & txtaddcountrycode.Text & "', '" & txtCountryName.Text & "')"
        SQLInsert2 = "INSERT INTO Countries ( country_code, country_name ) VALUES " & SQLInsert

        conn.Execute (SQLInsert2)
       
        conn.Close
        Set conn = Nothing
       
        MsgBox ("The country " & txtCountryName & " has been entered into the database.")
        frmAddCountryCode.Hide
        txtaddcountrycode.Text = ""
        txtCountryName.Text = ""
    Else
        'otherwise tells the use the name or code is already used
        MsgBox ("Either the country name or the counrty code is already used in the database. You can not enter this country at the moment.")
    End If
End If
End Sub



Many regards
S.
0
 

Author Comment

by:ls3ag
ID: 8162316
Please can anyone help me with the above??????? I couldn't come up with something ...... HHHHEEEEELLLLPPPP!
0
 
LVL 2

Expert Comment

by:Sweat
ID: 8162598
ls3aq,

Deleting a row from a recordset is not complicated.

It can be done in two ways.

1.   Find the row you want to delete by building a SQL statement.  Once you've located the exact row, use the recordset structure to delete it.

   rs.Delete

or

2.   Build a SQL statement that will only find the row you want to delete and then use the execute statement.

   conn.Execute (sSQL)

Let me know if you do not understand this or need more clarification.

I am more than happy to help out as most experts here are. But please keep in mind that you've now asked a total of three questions and have not accepted any answer nor have you assigned new points to the additional two questions that were not part of your original question.

It is important to keep these questions as clear as possible.

Sweat

0
 

Author Comment

by:ls3ag
ID: 8162727
ok sweat i got your point about the accepted as an answer of a question. I prefer to show me how you can do that by the second method of SQL... I hope to help me out please and state it as much clearer as you can.... Is it your method similar to the one that i did above? Can you help me on doing that delete in the same (similar) way as the statement that i ve done?
 
Thank you sweat for your understanding!
0
 
LVL 2

Accepted Solution

by:
Sweat earned 1000 total points
ID: 8163150
ls3aq,

To use the .Execute method you need to build a SQL statement that can only find that record or those records you want to have deleted.

Dim sSql$

sSql = "DELETE FROM Countries WHERE "
sSql = sSql & "country_code='" & Text1.Text & "' AND "
sSql = sSql & "country_name='" & Text2.Text & "'"

Conn.Execute (sSql)

Text1 and text2 would, in this example, be text boxes holding the countrycode and the countryname.  I'm also assuming that both columns are Text as noted by my surrounding them with the single quote character "'".  If the country_code is numeric, do not include the single quote character around the variable Text1.Text.

In the above sql statement, all rows with the same country_code and the same country_name will be deleted.  While this may only represent a single row, there may perhaps be times when you need to delete all of the records with the same country_code.  In that situation, you would only include a WHERE clause with the country_code and no country_name.

Certainly you'll need to test the above sql statement in your testing environment.

There are some very good examples of SQL coding standards and examples on the Microsoft web site www.microsoft.com if you have a few minutes to check those out also.

Hope this helps,

Sweat


0
 

Expert Comment

by:CleanupPing
ID: 8900144
ls3ag:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 9692884
Moderator, my recommended disposition is:

    Accept Sweat's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

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!

Question has a verified solution.

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

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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

762 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