Look for Comma or Space 2

airboat · Apr 12, 2011

I am looking for a phrase based on if a field (NAME) has 4 characters or more. If the field starts with 4 characters or more. I want to take everything up to the first space or the end of the field, whichever comes first, and put that into another field called (SECNAME).

Heather

dhookom · Apr 12, 2011

Heather,
Please take a few seconds to provide the actual table and field names as well as current values and desired values.

Duane
Hook'D on Access
MS Access MVP

TheAceMan1 · Apr 12, 2011

How are ya airboat . . .

Here's a function you can use:

Code:

[blue]Public Function GetPhrase(ByVal Dat)
   Dim idxSpc As Long, idxComma As Long, idxUse As Long
   
   GetPhrase = Dat [green]'Preset to return passed Dat[/green]
   Dat = Nz(Dat, "") [green]'Take care of Nulls for InStr![/green]
   
   If Len(Dat) > 3 Then
      idxSpc = InStr(Dat, " ")
      idxComma = InStr(Dat, ",")
      
      [green]'Which came 1st space or comma?[/green]
      If idxSpc > 0 And idxComma > 0 Then
         If idxSpc < idxComma Then
            idxUse = idxSpc
         Else
            idxUse = idxComma
         End If
      ElseIf idxSpc > 0 And idxComma = 0 Then
         idxUse = idxSpc
      Else
         idxUse = idxComma
      End If
      
      If idxUse > 0 Then GetPhrase = Left(Dat, idxUse - 1)
   End If
   
End Function[/blue]

How you use the function depends on your answer to [blue]dhookom[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

airboat · Apr 13, 2011

Duane,

Attached is a file for you to use with the proper field names. The field the value is coming from is called "PropertyDescription_1". The results are going into a field called "SubDivision". I'm not sure if I explained myself the right way. I just want to make sure I did. I want to find the the word Subdivision in the propertydescription_1 field. There might be a comma or a space or both or one or the other. When I find the word subdivision, I want to take the next 2 words and put them into the field subdivision.

Bob

dhookom · Apr 13, 2011

I'm not about to download your file. Did you even attempt to use the suggestion from TheAceMan1?

It is really fairly easy to type the requested information into a reply. TheAceMan1 actually went through the effort of providing a function and using TGML to format it nicely.

Duane
Hook'D on Access
MS Access MVP

airboat · Apr 13, 2011

Aceman1,

Thank you for help us with the code. I basically would like to know how to run with code and place it into the subdivision field.
The data in question is a field called propertydescription_1 and place it into the subdivision field.

Bob

dhookom · Apr 13, 2011

Bob,
To test the function, create a new module and paste the code into it and save the module as "modStringFunctions". Then open the debug window (press Ctrl+G) and enter

Code:

? GetPhrase("value pasted here from PropertyDescription_1")

When you press enter, do you see the value you expect?

Please reply with your results and further questions.

Duane
Hook'D on Access
MS Access MVP

MajP · Apr 13, 2011

Code:

Public Function getSubDivPlusTwo(varDesc As Variant) As String
  'Finds the word subD* and the next two words
  Dim aSub() As String
  Dim i As Integer
  If Not IsNull(varDesc) Then
    If InStr(varDesc, "SubD") > 0 Then
      varDesc = Trim(varDesc)
      varDesc = Mid(varDesc, InStr(varDesc, "SubD"))
      aSub = Split(varDesc, " ")
      getSubDivPlusTwo = "SubDivision"
      If UBound(aSub) = 0 Then Exit Function
      For i = 1 To UBound(aSub)
        getSubDivPlusTwo = getSubDivPlusTwo & " " & Trim(aSub(i))
        Debug.Print i
        If i = 2 Then Exit For
      Next i
    Else
      getSubDivPlusTwo = varDesc
    End If
  End If
End Function

Code:

UPDATE tblCleanProperties SET tblCleanProperties.Subdivision = getSubDivPlusTwo([descriptionNew])
WHERE (((tblCleanProperties.descriptionNew) Like "*SubD*"));

Results

Code:

Subdivision
SUBDIVISION HIDDEN CREEK
SUBDIVISION HONEY LAKE
SUBDIVISION
SUBDIVISION IN
SUBDIVISION HONEY LAKE
SUBDIVISION CITY OF
SUBDIVISION THIRD ADDITION
SUBDIVISION BARABOO, LOT
SUBDIVISION BROWN'S ADDITION,
SUBDIVISION PARK RIDGE,
SUBDIVISION
SUBDIVISION HONEY LAKE
SUBDIVISION
SUBDIVISION FOURTH MAP
SUBDIVISION ORIGINAL PLAT,
SUBDIVISION WILLOW RUN
SUBDIVISION GRAND GENEVA
SUBDIVISION BARABOO, LOT
SUBDIVISION JOHN HOPPE
SUBDIVISION WARREN PARK,
SUBDIVISION
SUBDIVISION PLEASANT HILL,
SUBDIVISION SECOND ADD.
SUBDIVISION FIRST
SUBDIVISION VICTORY HEIGHTS
SUBDIVISION CITY OF
SUBDIVISION GRAND GENEVA
SUBDIVISION WILLOW RUN
SUBDIVISION HONEY LAKE
SUBDIVISION VILLAGE OF
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION
SUBDIVISION REPLAT OF
SUBDIVISION
SUBDIVISION LYON'S, LOT
SUBDIVISION REPLAT OF
SUBDIVISION PARKSIDE CONDOMINIUMS,
SUBDIVISION JOHN HOPPE,
SUBDIVISION BAYWOOD ESTATES,
SUBDIVISION CSM 5088,
SUBDIVISION CERTIFIED SURVEY
SUBDIVISION
SUBDIVISION FIRST ADDT
SUBDIVISION RITZ CORNERS
SUBDIVISION PRAIRIE ESTATES,
SUBDIVISION
SUBDIVISION THIR
SUBDIVISION THI
SUBDIVISION
SUBDIVISION WARREN PARK
SUBDIVISION ROBERT R.
SUBDIVISION BLUE WING
SUBDIVISION
SUBDIVISION INTERLAKEN SUB,
SUBDIVISION FOURTH MAP
SUBDIVISION
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION
SUBDIVISION OAKWOOD HEIGHTS,
SUBDIVISION
SUBDIVISION
SUBDIVISION GRAND GENEVA
SUBDIVISION CITY OF
SUBDIVISION ROLLING MEADOWS,
SUBDIVISION FAIRFIELD WI
SUBDIVISION FORMERLY ADAMS,
SUBDIVISION TIMOTHY CUMMINGS
SUBDIVISION JOHN HOPPE,
SUBDIVISION LANGDON'S SUB,
SUBDIVISION OF
SUBDIVISION LANGDON'S SUB.,
SUBDIVISION GRAND GENEVA
SUBDIVISION
SUBDIVISION MOTT'S FIRST
SUBDIVISION
SUBDIVISION
SUBDIVISION
SUBDIVISION
SUBDIVISION
SUBDIVISION
SUBDIVISION CRYSTAL BOWL
SUBDIVISION
SUBDIVISION
SUBDIVISION WALWORTH
SUBDIVISION CITY OF
SUBDIVISION STANLEY'S SUBD.,
SUBDIVISION CASE'S SUB,
SUBDIVISION PECK'S SUB.,
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION
SUBDIVISION GRAND GENEVA
SUBDIVISION
SUBDIVISION MRS. V
SUBDIVISION J.A. VERTEIN
SUBDIVISION ROLLING MEADOWS,
SUBDIVISION CITY OF
SUBDIVISION CRAWFORD'S ADD,
SUBDIVISION CITY OF
SUBDIVISION JOHN HOPPE,
SUBDIVISION GRAND GENEVA
SUBDIVISION CSM 1684,
SUBDIVISION FIRST ADDITICJN
SUBDIVISION CSM 4275,
SUBDIVISION SPRUCE HAVEN,
SUBDIVISION CITY OF
SUBDIVISION CSM 5394,
SUBDIVISION HEIN'S ADD.,
SUBDIVISION ENGLISH'S SUBD.,
SUBDIVISION 1ST ADD.
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION FORMERLY ADAMS,
SUBDIVISION CSM 5410,
SUBDIVISION GRAND GENEVA
SUBDIVISION
SUBDIVISION
SUBDIVISION FIRST ADD.
SUBDIVISION 2ND ADD.
SUBDIVISION TITUS ADDITION,
SUBDIVISION MOTT'S ADDITION,
SUBDIVISION RITZ CORNERS
SUBDIVISION RASMUSSEN SUBDIVISION,
SUBDIVISION RITZ CORNERS
SUBDIVISION
SUBDIVISION CITY OF
SUBDIVISION WILLOW RUN
SUBDIVISION ERNSTMEYER ACRES
SUBDIVISION WARREN PARK,
SUBDIVISION PLAT OF
SUBDIVISION
SUBDIVISION GATEWAY COMMUNITY
SUBDIVISION GRAND GENEVA
SUBDIVISION RITZ CORNERS
SUBDIVISION FOREST VIEW
SUBDIVISION HUNTINGTON PARK
SUBDIVISION ROSEMEYER CONDOMINIUM,
SUBDIVISION
SUBDIVISION 3R
SUBDIVISION NORTH LAKE
SUBDIVISION VERTEIN'S ADDITION,
SUBDIVISION LAUDERDALE LAKES
SUBDIVISION FOREST VIEW
SUBDIVISION FOREST VIEW
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION WESTDAYL, LOT
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION THE PRAIRIE,
SUBDIVISION BAKER'S ADD.,
SUBDIVISION
SUBDIVISION
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION THE OAKS,
SUBDIVISION HANOVER VILLAGE
SUBDIVISION
SUBDIVISION NANCY CUMMINGS,
SUBDIVISION GRAND GENEVA
SUBDIVISION TURNER &
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION 2ND ADDITION
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA,
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION WILLOW RUN
SUBDIVISION THOMASSECOND ADDITION,
SUBDIVISION WATER TOWER
SUBDIVISION UNIVERSITY HEIGHTS,
SUBDIVISION DWINNELLS ADDN.,
SUBDIVISION GRAND GENEVA
SUBDIVISION

Note the word SUBDIVISION by itself means it was at the end of the description

MajP · Apr 13, 2011

So my point is that your logic is incorrect.

At a minimum you need another function called

subDivMinusTwo
'If the word subdivision is the last word take the two previous words

Example:
LOTS 14 THROUGH 18 BLOCK A CRYSTAL BOWL SUBDIVISION
output
Crystal Bowl Subdivision

airboat · Apr 13, 2011

>>dhookom<<

I am not sure what I am doing wrong, but no matter what I try I am not getting any results. And it is probably something I am doing wrong. I tried to run the code and I get nothing. I am very new to Access and most of this is still over my head. I am trying to understand everything as I go along. And I just can't make it work for me.

Bob

airboat · Apr 13, 2011

>>MajP<<

You have done so much to help us with this. It is just another way we were trying to get through and clean up this data. What I did, and tell me if I am correct, I added the first code into a module. I used the second code and created a query.

When I run the query does that look to the module for what it needs to update the field? I am learning and trying to follow so I at least have an idea what is going on. I did this in a sample database just to use a smaller dataset and these were my results:

SubDivision

SubDivision OF LOT
SubDivision GATEWAY MEADOWS,
SubDivision GATEWAY MEADOWS,
SubDivision CHRISTOPHERSONS'S SUBDIVISION,

How do I get these same results without the actual word "SubDivision" within the result or the comma at the end?

Bob

MajP · Apr 13, 2011

new function that calls two functions.
One to handle
RITZ CORNERS SUBDIVISION
and one to handle
OAKWOOD HEIGHTS SUBDIVISION

Code:

Public Function getSubDivForwardBackward(varDesc As Variant) As String
  Dim aSub() As String
  Dim i As Integer
  'make sure not null input
  If Not IsNull(varDesc) Then
     'Check if SubD in the description
     If InStr(varDesc, "SubD") > 0 Then
        varDesc = Trim(varDesc)
        aSub = Split(varDesc, " ")
        'Check to see if SUBD is last word
        If InStr(aSub(UBound(aSub)), "SubD") > 0 Then
           getSubDivForwardBackward = getSubDivMinusTwo(varDesc)
        Else
           'SUBD not in last word
           getSubDivForwardBackward = getSubDivPlusTwo(varDesc)
        End If
      Else
        getSubDivForwardBackward = varDesc
      End If
    End If
End Function

Code:

Public Function getSubDivPlusTwo(varDesc As Variant) As String
  'Finds the word subD* and the next two words
  Dim aSub() As String
  Dim i As Integer
  Dim tempDesc As String
  
  If Not IsNull(varDesc) Then
    tempDesc = varDesc
    If InStr(varDesc, "SubD") > 0 Then
      varDesc = Trim(varDesc)
      varDesc = Mid(varDesc, InStr(varDesc, "SubD"))
      aSub = Split(varDesc, " ")
      getSubDivPlusTwo = "SUBDIVISION"
      
      If UBound(aSub) = 0 Then
         getSubDivPlusTwo = getSubDivMinusTwo(tempDesc)
         Debug.Print getSubDivPlusTwo
      Else
        For i = 1 To UBound(aSub)
          getSubDivPlusTwo = getSubDivPlusTwo & " " & Trim(aSub(i))
          'Debug.Print i
          If i = 2 Then Exit For
        Next i
      End If
    Else
      getSubDivPlusTwo = varDesc
    End If
  End If
End Function

Code:

Public Function getSubDivMinusTwo(varDesc As Variant) As String
  'Finds the word subD* and the previous two words
  'When subd at end of description
  Dim aSub() As String
  Dim i As Integer
  If Not IsNull(varDesc) Then
    If InStr(varDesc, "SubD") > 0 Then
      varDesc = Trim(varDesc)
      aSub = Split(varDesc, " ")
     If Not InStr(aSub(UBound(aSub)), "SUBD") > 0 Then
        Exit Function
      Else
        For i = UBound(aSub) To 0 Step -1
          getSubDivMinusTwo = Trim(aSub(i)) & " " & getSubDivMinusTwo
           'Debug.Print i
          If i = UBound(aSub) - 2 Then Exit For
        Next i
      End If
    Else
      getSubDivMinusTwo = varDesc
    End If
  End If
End Function

New query

Code:

UPDATE tblCleanProperties SET tblCleanProperties.Subdivision = getSubDivForwardBackward([descriptionNew])
WHERE (((tblCleanProperties.descriptionNew) Like "*SubD*"));

Pretty good results

Code:

Subdivision
VIEW ESTATES SUBDIVISION 
VI M SUBDIRVISICJN 
SUBDIVISION WILLOW RUN
SUBDIVISION WILLOW RUN
SUBDIVISION WILLOW RUN
SUBDIVISION WILLOW RUN
SUBDIVISION WESTDAYL, LOT
SUBDIVISION WATER TOWER
SUBDIVISION WARREN PARK,
SUBDIVISION WARREN PARK,
SUBDIVISION WARREN PARK
SUBDIVISION WALWORTH
SUBDIVISION VILLAGE OF
SUBDIVISION VICTORY HEIGHTS
SUBDIVISION VERTEIN'S ADDITION,
SUBDIVISION UNIVERSITY HEIGHTS,
SUBDIVISION TURNER &
SUBDIVISION TITUS ADDITION,
SUBDIVISION TIMOTHY CUMMINGS
SUBDIVISION THOMASSECOND ADDITION,
SUBDIVISION THIRD ADDITION
SUBDIVISION THIR
SUBDIVISION THI
SUBDIVISION THE PRAIRIE,
SUBDIVISION THE OAKS,
SUBDIVISION STANLEY'S SUBD.,
SUBDIVISION SPRUCE HAVEN,
SUBDIVISION SECOND ADD.
SUBDIVISION ROSEMEYER CONDOMINIUM,
SUBDIVISION ROLLING MEADOWS,
SUBDIVISION ROLLING MEADOWS,
SUBDIVISION ROBERT R.
SUBDIVISION RITZ CORNERS
SUBDIVISION RITZ CORNERS
SUBDIVISION RITZ CORNERS
SUBDIVISION RITZ CORNERS
SUBDIVISION REPLAT OF
SUBDIVISION REPLAT OF
SUBDIVISION RASMUSSEN SUBDIVISION,
SUBDIVISION PRAIRIE ESTATES,
SUBDIVISION PLEASANT HILL,
SUBDIVISION PLAT OF
SUBDIVISION PECK'S SUB.,
SUBDIVISION PARKSIDE CONDOMINIUMS,
SUBDIVISION PARK RIDGE,
SUBDIVISION ORIGINAL PLAT,
SUBDIVISION OF
SUBDIVISION OAKWOOD HEIGHTS,
SUBDIVISION NORTH LAKE
SUBDIVISION NANCY CUMMINGS,
SUBDIVISION MRS. V
SUBDIVISION MOTT'S FIRST
SUBDIVISION MOTT'S ADDITION,
SUBDIVISION LYON'S, LOT
SUBDIVISION LAUDERDALE LAKES
SUBDIVISION LANGDON'S SUB.,
SUBDIVISION LANGDON'S SUB,
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION LAKE WANDAWEGA
SUBDIVISION JOHN HOPPE,
SUBDIVISION JOHN HOPPE,
SUBDIVISION JOHN HOPPE,
SUBDIVISION JOHN HOPPE
SUBDIVISION J.A. VERTEIN
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION INTERLAKEN, LOT
SUBDIVISION INTERLAKEN SUB,
SUBDIVISION IN
SUBDIVISION HUNTINGTON PARK
SUBDIVISION HONEY LAKE
SUBDIVISION HONEY LAKE
SUBDIVISION HONEY LAKE
SUBDIVISION HONEY LAKE
SUBDIVISION HIDDEN CREEK
SUBDIVISION HEIN'S ADD.,
SUBDIVISION HANOVER VILLAGE
SUBDIVISION GRAND GENEVA,
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GRAND GENEVA
SUBDIVISION GATEWAY COMMUNITY
SUBDIVISION FOURTH MAP
SUBDIVISION FOURTH MAP
SUBDIVISION FORMERLY ADAMS,
SUBDIVISION FORMERLY ADAMS,
SUBDIVISION FOREST VIEW
SUBDIVISION FOREST VIEW
SUBDIVISION FOREST VIEW
SUBDIVISION FIRST ADDT
SUBDIVISION FIRST ADDITICJN
SUBDIVISION FIRST ADD.
SUBDIVISION FIRST
SUBDIVISION FAIRFIELD WI
SUBDIVISION ERNSTMEYER ACRES
SUBDIVISION ENGLISH'S SUBD.,
SUBDIVISION DWINNELLS ADDN.,
SUBDIVISION CSM 5410,
SUBDIVISION CSM 5394,
SUBDIVISION CSM 5088,
SUBDIVISION CSM 4275,
SUBDIVISION CSM 1684,
SUBDIVISION CRYSTAL BOWL
SUBDIVISION CRAWFORD'S ADD,
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CITY OF
SUBDIVISION CERTIFIED SURVEY
SUBDIVISION CASE'S SUB,
SUBDIVISION BROWN'S ADDITION,
SUBDIVISION BLUE WING
SUBDIVISION BAYWOOD ESTATES,
SUBDIVISION BARABOO, LOT
SUBDIVISION BARABOO, LOT
SUBDIVISION BAKER'S ADD.,
SUBDIVISION 3R
SUBDIVISION 2ND ADDITION
SUBDIVISION 2ND ADD.
SUBDIVISION 1ST ADD.
RITZ CORNERS SUBDIVISION 
RITZ CORNERS SUBDIVISION 
OAKWOOD HEIGHTS SUBDIVISION 
OAKWOOD HEIGHTS SUBDIVISICIJN 
OAKLAND HEIGHTS SUBD 
OAKEWOOD HEIGHTS SUBDIVISION 
NANCY CUMMINGS SUBDIVISIOLL 
MARSHALL HEIGHTS SUBDIVISION 
MAP INTERLAKEN SUBDIVISION 
LOTS7891011BLOCK15 5KANSEN SUBDIVISI0N 
LAKE WANDAWEGA SUBDIVISION, 
LAKE WANDAWEGA SUBDI 
LAKE WANDAWEGA SUBD 
L, PITJEVIEW SUBDIVISION 
JUNGE'S RODNEYDALE SUBDIVI 
JOHN HOPPE SUBDIVISION 
JOHN HCJPPE SUBDIVISICIJN 
H. JACOBIS SUBD1IVISION 
G TUTTLE'S SUBDIVISION 
ERNSTMEYER ACRES SUBD1IVISICJN 
CRYTAL BOWL SUBDIVISION 
CRYSTAL BOWL SUBDIVISION 
CRYSTAL B0WL SUBDIVISISION 
9J INTERLAKEN SUBDI1IVISION 
6 SXANSSN SUBD1V1S10N 
5, ENGLISHIS SUBDTVISION 
5 CA3CS SUBDIV1S1CN 
2, STANLEY'S SUBDIVISION 
2, RIVERCREST SUBDIVIS 
2, LEHMANIS SUBDIVISION 
103 WANDAWEGA SUBDIVISION 
0L0CK5 0URKE5TCM72PBELLS SUBD 
- B SUBDIVISIIION

I purposedly left the Subdivision in so that you can see what you have. Again this can be cleaned using an update query.
replace SUBDIVISION with "". I showed this in a previous post.

Getting rid of commas and numbers could be done with more code, but again I would continue to clean with update queries instead.
Example:

UPDATE tblCleanProperties SET tblCleanProperties.Subdivision = Replace([subdivision],",","");

UPDATE tblCleanProperties SET tblCleanProperties.Subdivision = Replace([subdivision],"SUBDIVISION","");

Code:

Subdivision
VIEW ESTATES  
VI M SUBDIRVISICJN 
 WILLOW RUN
 WILLOW RUN
 WILLOW RUN
 WILLOW RUN
 WESTDAYL LOT
 WATER TOWER
 WARREN PARK
 WARREN PARK
 WARREN PARK
 WALWORTH
 VILLAGE OF
 VICTORY HEIGHTS
 VERTEIN'S ADDITION
 UNIVERSITY HEIGHTS
 TURNER &
 TITUS ADDITION
 TIMOTHY CUMMINGS
 THOMASSECOND ADDITION
 THIRD ADDITION
 THIR
 THI
 THE PRAIRIE
 THE OAKS
 STANLEY'S SUBD.
 SPRUCE HAVEN
 SECOND ADD.
 ROSEMEYER CONDOMINIUM
 ROLLING MEADOWS
 ROLLING MEADOWS
 ROBERT R.
 RITZ CORNERS
 RITZ CORNERS
 RITZ CORNERS
 RITZ CORNERS
 REPLAT OF
 REPLAT OF
 RASMUSSEN 
 PRAIRIE ESTATES
 PLEASANT HILL
 PLAT OF
 PECK'S SUB.
 PARKSIDE CONDOMINIUMS
 PARK RIDGE
 ORIGINAL PLAT
 OF
 OAKWOOD HEIGHTS
 NORTH LAKE
 NANCY CUMMINGS
 MRS. V
 MOTT'S FIRST
 MOTT'S ADDITION
 LYON'S LOT
 LAUDERDALE LAKES
 LANGDON'S SUB.
 LANGDON'S SUB
 LAKE WANDAWEGA
 LAKE WANDAWEGA
 LAKE WANDAWEGA
 JOHN HOPPE
 JOHN HOPPE
 JOHN HOPPE
 JOHN HOPPE
 J.A. VERTEIN
 INTERLAKEN LOT
 INTERLAKEN LOT
 INTERLAKEN LOT
 INTERLAKEN LOT
 INTERLAKEN SUB
 IN
 HUNTINGTON PARK
 HONEY LAKE
 HONEY LAKE
 HONEY LAKE
 HONEY LAKE
 HIDDEN CREEK
 HEIN'S ADD.
 HANOVER VILLAGE
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GRAND GENEVA
 GATEWAY COMMUNITY
 FOURTH MAP
 FOURTH MAP
 FORMERLY ADAMS
 FORMERLY ADAMS
 FOREST VIEW
 FOREST VIEW
 FOREST VIEW
 FIRST ADDT
 FIRST ADDITICJN
 FIRST ADD.
 FIRST
 FAIRFIELD WI
 ERNSTMEYER ACRES
 ENGLISH'S SUBD.
 DWINNELLS ADDN.
 CSM 5410
 CSM 5394
 CSM 5088
 CSM 4275
 CSM 1684
 CRYSTAL BOWL
 CRAWFORD'S ADD
 CITY OF
 CITY OF
 CITY OF
 CITY OF
 CITY OF
 CITY OF
 CITY OF
 CITY OF
 CERTIFIED SURVEY
 CASE'S SUB
 BROWN'S ADDITION
 BLUE WING
 BAYWOOD ESTATES
 BARABOO LOT
 BARABOO LOT
 BAKER'S ADD.
 3R
 2ND ADDITION
 2ND ADD.
 1ST ADD.
RITZ CORNERS  
RITZ CORNERS  
OAKWOOD HEIGHTS  
OAKWOOD HEIGHTS SUBDIVISICIJN 
OAKLAND HEIGHTS SUBD 
OAKEWOOD HEIGHTS  
NANCY CUMMINGS SUBDIVISIOLL 
MARSHALL HEIGHTS  
MAP INTERLAKEN  
LOTS7891011BLOCK15 5KANSEN SUBDIVISI0N 
LAKE WANDAWEGA  
LAKE WANDAWEGA SUBDI 
LAKE WANDAWEGA SUBD 
L PITJEVIEW  
JUNGE'S RODNEYDALE SUBDIVI 
JOHN HOPPE  
JOHN HCJPPE SUBDIVISICIJN 
H. JACOBIS SUBD1IVISION 
G TUTTLE'S  
ERNSTMEYER ACRES SUBD1IVISICJN 
CRYTAL BOWL  
CRYSTAL BOWL  
CRYSTAL B0WL SUBDIVISISION 
9J INTERLAKEN SUBDI1IVISION 
6 SXANSSN SUBD1V1S10N 
5 ENGLISHIS SUBDTVISION 
5 CA3CS SUBDIV1S1CN 
2 STANLEY'S  
2 RIVERCREST SUBDIVIS 
2 LEHMANIS  
103 WANDAWEGA  
0L0CK5 0URKE5TCM72PBELLS SUBD 
- B SUBDIVISIIION

TheAceMan1 · Apr 13, 2011

airboat . . .

In your post origination you said:

airboat said:
[blue]I am looking for a phrase based on if a field (NAME) has 4 characters or more. If the field starts with 4 characters or more. [purple]I want to take everything up to the first space or the end of the field, whichever comes first, and put that into another field called (SECNAME).[/purple][/blue]

Then in your post dated [blue]13 Apr 11 11:34[/blue] you said:

airboat said:
[blue]I want to find the the word Subdivision in the propertydescription_1 field. There might be a comma or a space or both or one or the other. When I find the word subdivision, I want to take the next 2 words and put them into the field subdivision.[/blue]

These are two completely different secnario's, the first (of which I based my code) was completely misleading. You need to be careful of this as [purple]it causes tipsters to waste their time.[/purple] [surprise]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

airboat · Apr 14, 2011

TheAceman and dhookom:

I am sorry if I upset you that was not my intention. The reason for the switch is because regardless of what I am doing, I do need it both ways. I probably should have put it into two different posts and I apologize for that. The route it ended up going seemed to be the lesser of two evils. The last thing I want to do is upset people that are surely doing a lot for somebody who doesn't have a clue at what he is doing. And on my end, Heather is stuck in between trying to help me out and work with me on this end, who is doing an excellent job considering she switched from years of dBase into Access with about 2 weeks behind her.

Bob

airboat · Apr 14, 2011

>>MajP<<

When I am running this against what I already have I assume it will not disturb any values that do not contain the word Subdivision, am I correct?

I can run it just like any of the other queries you helped me to setup? And in this case the query goes out and uses the Module that was setup, right?

Bob

airboat · Apr 14, 2011

>>MajP<<

Answered it for myself. Thanks again for yet more help with this!! I was trying to find a way to send you a PM but this forum doesn't seem to have that ability...

Bob

TheAceMan1 · Apr 14, 2011

airboat said:
[blue]I am sorry if I upset you that was not my intention.[/blue]

Do not feather yourself. I'm sure your intent was well meant. Just making you aware for future reference.

Glad to see you've reached resolution! [thumbsup2]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Look for Comma or Space 2

airboat

Technical User

dhookom

Programmer

TheAceMan1

Programmer

airboat

Technical User

dhookom

Programmer

airboat

Technical User

dhookom

Programmer

MajP

Technical User

MajP

Technical User

airboat

Technical User

airboat

Technical User

MajP

Technical User

TheAceMan1

Programmer

airboat

Technical User

airboat

Technical User

airboat

Technical User

TheAceMan1

Programmer

Similar threads

Part and Inventory Search

Sponsor