how to query using text box with vba

[Cut_EliaRahmi]

Dear all vba programmer

I have a shapefile with 3 fields: fid, shape, facilities. I wanna query based on facilities field, by typing 1 or 2 beginning words in the text box. The problem for me is the value in the facilities field don???t have first similar word. for example, I wanna search all of school, so how to query? Below is the example of my data.

FID SHAPE FACILITIES
0 Point SD Negeri Buket Meutuah -->School
1 Point RS Langsa -->Hospital
2 Point Bank Syariah Mandiri -->Bank
3 Point SD Alur Merbau -->School
4 Point Rumah Sakit cut Meutia -->Hospital
5 Point Kantor Samsat Langsa -->Police office
6 Point Mesjid Raya Bujok -->Mosque
7 Point MAN Langsa -->School
8 Point SMP Negeri 7 Langsa -->School
9 Point Kantor Dinas SDA -->Office
10 Point BRI Langsa -->Bank
11 Point Rumah Sakit Umum Langsa -->Hospital
12 Point Pesantren Bustanul Ulum -->School
13 Point Kantor Polisi Militer -->Police office
14 Point Kantor Kecamatan -->Office
??? ???. ???.
Wish somone can help. Thank's in advance

[DuncanHornby]

Hello,

First of all can I correct your English. "Wanna" is not an English word, you should be saying "I want to create a query..." not "I wanna create a query...". OK English lesson over.

In your Shapefile example does the facilities field always end with "-->School" or have you put that in as an explanation? If it does then you want to create a QueryFilter to return a cursor for all rows that end with that text sequence.

You can find a simple example of how to create a cursor in the VBA developers Help under the sample section, the page is called "Perform an attribute query".  The WhereClause string can be determined easily by doing the query in ArcMap so you get the syntax correct, it should be something like:

"FACILITIES" LIKE '%-->School'

[AlexanderGray]

Duncan solution is good.  If it were me I'd wanna parse out the data first.  Specially if I wanted to use it for a lot of querying and the data wasn't subject to a lot of updates.  If you can create a facilityType column and take all the parts after "-->" (field calculator with some vbscript can do that.)  Then you could query the facility types with the arcmap attribute selection and wouldn't have to write a stitch of vba code.  Indexing the facilities type column would help performance with large datasets.

[DuncanHornby]

I saw that!

😛

[Cut_EliaRahmi]

I'm sorry, I didn't use formal english:)
"-->School" is an explanation, the data is only consist of 3 fields, i.e FID, SHAPE and FACILITIES.
Ex:
FID  SHAPE   FACILITIES
0     Point     SD Negeri Buket Meutuah

"SD Negeri Buket Meutuah" is a school. Among 1 and other schools don't have the similar word, so do the other facilities. can I use "LIKE '%"?
Thanks for sugestion.

[DuncanHornby]

So you have no consistent naming convention in the facilities field? I think Alexander's advice would help you. Create a new field that codes the type of facility this would simplify your query.

[AlexanderGray]

If there is no way to know what facilities are schools, you can't query data that is not there.

[Cut_EliaRahmi]

Thanks for suggestion. I�??ve written code to add new field named �??TYPE�?� and determine the type of facilities using select case. The problem for me is when updating the value of TYPE field. I don�??t know how to set value: if the value in FACILITIES field contents the word SD or SMP or SMA or MAN, so the value in TYPE field is School, etc. Now, that�??s the problem for me before querying, wish someone can help. Below is my code so far:

Dim pMxDoc As IMxDocument
Dim pFLayer As IFeatureLayer
Dim pFClass As IFeatureClass
Set pMxDoc = ThisDocument
Set pFLayer = pMxDoc.FocusMap.Layer(0)
Set pFClass = pFLayer.FeatureClass

Dim pField As IFieldEdit
Set pField = New field
pField.Name = "TYPE"
pField.Type = esriFieldTypeString
pField.Length = 20
pFClass.AddField pField

Dim pFCursor As IFeatureCursor
Set pFCursor = pFClass.Update(Nothing, True)
Dim pFeature As IFeature
Set pFeature = pFCursor.NextFeature

Dim ketFacilities As String
Select Case ketFacilities
    Case 0
        ketFacilities = "School"
    Case 1
        ketFacilities = "Office"
    Case 2
        ketFacilities = "Hospital"
    Case 3
        ketFacilities = "Mosque"
    Case 4
        ketFacilities = "City Boundary"
    Case 5
        ketFacilities = "Bridge"
    Case 6
        ketFacilities = "Bank"
End Select

Thanks