How to Update 70,000++ Records

[UCBFireCenter]

Sorry, my VB code actually reads:

Option Compare Database
Public Function ClassType(InOwner1 As String, FACRES As Double) As String
'-- Examine the Owner field and return the Class Code
If InStr(1, InOwner1, "CALIFORNIA STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "DISTRICT") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HUMBOLDT COUNTY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CITY OF") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "YUROK") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "HOOPA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "UNITED STATES OF AMERICA") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "CALIFORNIA THE STATE") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "COMMUNITY") Then
ClassType = "GOVT"
ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "BARNUM TIMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EMMERSON R H & SON") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SOPER-WHEELER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "SIERRA PACIFIC HOLDING") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "THE PACIFIC LUMBER") Then
ClassType = "FOREST INDUSTRY"
ElseIf InStr(1, InOwner1, "EEL RIVER SAWMILLS") Then
ClassType = "FOREST INDUSTRY"
ClassType = "Unknown"
ElseIf InStr(1, InOwner1, "TRUST") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "COMPANY") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "PROPERTIES") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "BANK") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LTD") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "LP") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "L P") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "DEVELOPMENT") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "INC") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
ElseIf InStr(1, InOwner1, "CORPORATION") Then
ClassType = "NON-INDUSTRY OR BUSINESS"
End If
End Function

[UCBFireCenter]

Okay...I think I solved the VB "EndIF" error...but it's not classifying the family farms by size (i.e. <10acres, and >10acres)

Good news: isn't crashing...woot!

[RuralGuy]

I and many other developers use a *great* free add-in called Smart Indenter. Not only does it make the code neater but it will then show you those missing or extra EndIf statements by the indenting.

[RuralGuy]

I used SmartIndenter and gave you an ELSE statement. I think your SQL statement should now work without an error. The UDF is not complete yet but the errors should be gone. Let me know.

Option Compare Database
Option Explicit
 
Public Function ClassType(InOwner1 As String, FACRES As Double) As String
'-- Examine the Owner field and return the Class Code
   If InStr(1, InOwner1, "CALIFORNIA STATE") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "DISTRICT") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "HUMBOLDT COUNTY") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "CITY OF") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "YUROK") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "HOOPA") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "UNITED STATES OF AMERICA") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "CALIFORNIA THE STATE") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "COMMUNITY") Then
      ClassType = "GOVT"
   ElseIf InStr(1, InOwner1, "SCOTIA PACIFIC COMPANY") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "BARNUM TIMBER") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "EMMERSON R H & SON") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "SOPER-WHEELER") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "SIERRA PACIFIC HOLDING") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "THE PACIFIC LUMBER") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "EEL RIVER SAWMILLS") Then
      ClassType = "FOREST INDUSTRY"
   ElseIf InStr(1, InOwner1, "TRUST") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "COMPANY") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "PROPERTIES") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "BANK") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "LTD") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "LP") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "L P") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "DEVELOPMENT") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "INC") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   ElseIf InStr(1, InOwner1, "CORPORATION") Then
      ClassType = "NON-INDUSTRY OR BUSINESS"
   Else
      ClassType = "Unknown"
   End If
End Function

[RuralGuy]

FYI, the names you give your parameters between the (parens) do not have to match anything. They apply to the UDF so you name them to help explain what the UDF is trying to accomplish.

[UCBFireCenter]

Hm...okay....

I put in the "OPTION EXPLICIT" command and...I don't think I'm getting exactly what I asked for...

So, I'm noticing a lot of errors...

For example "BUSCH R E JR" is currently classified as "GOVT" (he's actually Family)....I can only assume this is because of the US in "BUSCH R E JR".

I have a line of code that calls for...

ElseIf InStr(1, InOwner1, "US") Then
ClassType = "GOVT"

Can you think of any way around this....might it matter that there is a line between "OPTION EXPLICIT" and the rest of the code?

[RuralGuy]

Option Explicit *insists* that before you use a variable that you define it first with a Dim statement. It is not the source of your current issue which I believe is in your returned records right? Change the "US" to either " US" or "US " and see what happens.

[RuralGuy]

BTW, Option Explicit will give you Compile time errors and not Run time errors and is great at catching typo's.

[UCBFireCenter]

I was reading about Dim...http://msdn.microsoft.com/en-us/library/7ee5a7s1(VS.80).aspx


Bad idea to use it in this case?

[UCBFireCenter]

I ask, because I tried what you said...I put a space in front and a space behind "US" (in two seperate statements)

...and I got

"YADACUS JOSEPH A" = "GOVT"

and

"SOUSA ANNE" = "GOVT"


Blargh...this is like digging a hole in a beach near the tide....sand just keeps coming back....

[RuralGuy]

Not at all. Are you getting compile errors? If so then where?

[RuralGuy]

Blargh...this is like digging a hole in a beach near the tide....sand just keeps coming back....

Quite right! You have chosen a challenging task. Your issue is with the returned records after the UDF runs right?

[RuralGuy]

BTW, I said try a space before the US " US" and a space after the US "US " and you could always try both " US ". Remember, the first match wins with this scheme.

[UCBFireCenter]

Nope, not getting compiling errors anymore...

So, put the space in front and behind. I have this problem with quite a few variables (i.e. LLP, LC, CORP)...any other way of fixing that? Because it seems that putting the space in front and behind works in some cases, but there are a great deal of misspelled entries (i.e. some genius entered half of my GOVT fields without spaces, so I have run-on genius statements like "USADISTRITCOURT" instead of "USA DISTRICT COURT")...

Thanks, again.

[RuralGuy]

So put the "USADISTRITCOURT" test before the "USA " test. BTW, "LLP", "LC" and "CORP" are *not* variables, they are string constants. InOwner1 and FACRES in the function are examples of variables, a term has a specific meaning in Visual Basic.