Query

amerifax · April 7th, 2011, 10:24 AM

I am trying to create a query which is totally new to me. I am including a picture that gives a better idea what I'm trying to do.

I'm drawing the value from field "PropertyDescription_1".

Note: The image shows lot as a field. That was a mistake "Lot" is a value within "PropertyDescription_1".

The condition is based on two fields.
PropertyDescription_1 (Field)
Contain Subd
Not Contain assessor,certified,parcel,CSM
Lot >2 (Lot has a space before number) Example:Lot Space 88

When building a query can you use a wildcard? One of our files has been scanned in so there are many misspellings within the field?

County Name (Field)
Contain Wash,Milw,Dodge

If the "Lot" value is a bit to much I can still make it work. It would be helpful. I have a database or Table with over 750,000 records. I would run this only every other day or so.

I hope you can help.

Thanks,

Bob

http://www.home2000.com/contain.jpg

gjgriffith · April 10th, 2011, 02:06 AM

Hello Bob,

I saw your post and I wanted to follow up. I looked over the image that you provided in the link. Here is what I'm thinking:

You asked: "When building a query can you use a wildcard?" Yes, absolutely, this is exactly what wildcards are designed to be used for and you appear to have used them correctly in your query, but the 2 lines for the "Lot" and "County Name" are incorrect, and this probably breaking your query.
You asked: "One of our files has been scanned in so there are many misspellings within the field?" Yep, I'm guessing you're trying to build a query to filter down the results in the "dirty data" that you need to get. This all makes sense and seems logical, except you need to fix the problem with the "Lot" and "County Name".
You said: "If the 'Lot' value is a bit to much I can still make it work. It would be helpful." So for the "Lot" (which I'm guessing is a field in this table), you need to add that field to the query and for the criteria for the "Lot" field, specify: >2.
As for the "County Name", again, I'm guessing this is a field in the table, you need to add that field to the query and for the criteria for the "County Name" field, specify the criteria on 3 separate lines as: ="Wash" (on the first criteria line), ="Milw" (on the second), ="Dodge" (on the third).

So, hopefully that all makes sense to you and my proposed solution will work for you. If you have more questions, please feel free to reply and I'll do what I can to help!

Sincerely,

Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

amerifax · April 11th, 2011, 10:13 PM

Thanks for the help. I will go over this tonight and let you know where I'm at Am.

Bob

amerifax · April 12th, 2011, 10:11 PM

Lot is a value in a field. It means the number of a lot in a subdivision. The field is loaded with bad garbage entered by recluss data entry:

"Town of Delafield section 10 Lot 3bc Five Fields Subdivision"

It's the 3 that of value. Lot is most always followed by a space and 1 or 2 values. Such as "Lot Space 12" or "Lot space 3" so the value I'm looking for is there the second or third value after the word "Lot" In the case of Lot 3bc it is only the "3' that's a value. If it was Lot 32c the value would be 32.
As you know we do not live in a perfect world. This data table is the most perfect example of that. It's up to me to be the vacuum. I know I can't get the data 100%. I'm only trying to gety as close to 100% without ending up in the loony bin.

Bob

malcolmdixon · April 13th, 2011, 06:27 AM

Hi Bob,

I created a table with your sample "garbage" data, then built a query to extract the lot number as you require. The SQL is as follows:

SELECT GarbageData.Garbage, Mid([Garbage],InStr([Garbage],"lot")) AS EndText, GetFirstNumber([EndText]) AS LotNum
FROM GarbageData;

Garbage is the field name. This basically gets the string starting from the word "lot".

GetFirstNumber is a VBA function that will extract the first instance of a positive whole number within a string. Please copy this code into a module.

Code:

Public Function GetFirstNumber(aString As String) As Integer
    'Returns the first instance of a positive whole number in a string.
    Dim aNumber As String
    Dim aChar As String
    Dim foundNumber As Boolean
    Dim endNumber As Boolean
    Dim index As Integer
    Dim result As Integer
    
    foundNumber = False
    index = 1
    result = -1
    
    While Not foundNumber And index <= Len(aString)
        aChar = Mid(aString, index, 1)
        foundNumber = IsNumeric(aChar)
        index = index + 1
    Wend
    
    If foundNumber Then
        endNumber = False
        aNumber = aChar
        While Not endNumber And index <= Len(aString)
            aChar = Mid(aString, index, 1)
            If IsNumeric(aChar) Then
                aNumber = aNumber & aChar
            Else
                endNumber = True
            End If
            index = index + 1
        Wend
        result = CInt(aNumber)
    End If
    GetFirstNumber = result
End Function

If no number is present -1 will be returned.

HTH.

Malc.