URGENT-How to find words starting with any alphabet and showing them..

I have a database in microsoft access(XP), i am able to access all data but i don't know how to find all words stating with any alphabet from a row, and how to showing them in a list box..,
i need an urgent help..
please help me..


  • [b][red]This message was edited by Moderator at 2003-11-21 8:57:54[/red][/b][hr]

    ok, we will start with finding words that start with a-z
    in your query(either access or inline sql in vb) you can have a select statement with something like the following:
    select fldwords from tblwords where fldwords Like "[a-z]"
    that will find the words in the fldwords column that start with any letter bettween a and z
    now lets say you want to find patterns that don't match(say you don't want to return records that start with a - z then you would do this:
    select fldwords from tblwords where fldwords Like "[!a-z]"
    that would not return records that started with a letter

    you can allso specify formats to seach for, lets say starting with a letter and followd by 2 numbers, would be like so:
    select fldwords from tblwords where fldwords Like "[a-z]##"

    that would find a value in the field that started with a letter and was folowed by 2 numbers.
    the following list are some more format specifiers for the like command:
    1)a*a - using this format will find multiple values inside the start and end char

    2)*adda* - this format will find anyting that contains the values inside the astiks.

    3)a[*]a - this format is specific it will find a*a - it allows you to search for special chars like the astriks

    4)aa* - find what starts with aa and then anything else

    5)a?a will find only one value inside the start and end eg, aaa, aba (abd would be false)

    6)a#a - finds a single didgit between the start and last char eg.a0a, a1a

    the other formats I'm aware have we covered earlier.

    ok ,now that you know how to search for your values we are going to assign the values into a list box in vb.
    (we will use inline sql - you could use a query instead)
    (note you will need a refference to microsoft activex data objects 2.1)
    (This is also asumming that you have a global connection object that is open, if you need help with that post and let me know.)
    examine the following code:
    'using ado object code
    Dim obj_RS as ADODB.Recordset

    'create recordset object
    set obj_RS = new ADODB.Recordset

    with obj_RS
    'use connection object
    .ActiveConnection = obj_Conn
    .CursorLocation = adUseClient
    'Read-Only recordset
    .CursorType = adOpenFowardOnly
    .Source = "Select fldWord from tblWords where fldWord Like "[a-z]"
    'check for end of file or beginning of file = true(no record found)
    If .BOF or .EOF = true then goto BadSearch
    'Loop through record set and load list box
    Do While Not .EOF
    'load listbox
    lstBoxName.addItem !fldWord
    'move to the next record
    .close ' close record set
    End With
    'destory recordset object - free up memory
    set obj_RS = Nothing
    exit sub
    msgbox "No record found"
    End Sub

    you could also use var's to deturmine your range in your where state ment like follows:
    (assume string1 = a and string2 = f)

    'declare 2 string values with only one space to hold search value, as well
    'as a string to hold the results for the search.
    dim string1, string2 as String * 1
    dim range as String

    string1 = text1.text
    string2 = text2.text
    result = "[" & string1 & "-" & string2 & "]"
    'result now equals "[a-f]"
    'you would then change the .Source of the above code to the following
    .Source = "Select fldWord from tblWord where fldWord Like " & result

    one important thing to not I belive that in access you are limited to 2 AND statements meaning you can only use 3 constraints in your where clause(other then with OR's)

    Hope that helps
    C:Dos Run
    Run Dos Run

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion