Access Ignorant 2

[Gooser]

I've been a dba for a while now, but always in SQL Server, never in Access. I have two columns that are text fields that are supposed to store the year a building was built, and the year of it's last remodel, respectively. We need to change these from their current state to their desired state as we move to a MSSQL database. The problem is because the fields are text, many records have things like [tt]1970's[/tt], [tt]1985-1990[/tt], [tt]Circa 1978[/tt], [tt]1985-1990[/tt], [tt]1983 & 1987[/tt], [tt]Unknown[/tt], and my personal favorite, [tt]Cirac 1978[/tt] [sic].

How can I query all the records in a text field that are not reasonable dates, i.e. don't fall between 1805-2007, inclusive and are anything other than 4 digits? I think I could do this in SQL Server quite easily, but I'm just stumbling around in Access.

The table looks like this:

risk_num  ri_year_built    ri_year_remod
--------- ---------------- --------------
1         1970	
2         1993	
3         Unknown.	
4         1964             2003
5         prior 1960's	
6         Unknown	
7         1956             2000
8         1963             1977-79 & 1987
9         1980's	
10        Circa 1950's	
11        1969, 1983, 1990	
12        1987             2
13        prior 1980's     1980's
14        Prior to 1925    1996
15        1967-68	
16        Prior to 1964	
17        Before 1994	
18        Pre 1975	
19        1977 & 1992	
20        1900             1994
21                         1995
22        1983             1988
23        1990's (est.)	
24        1990's	
25        Pre 1985	
26        1954             1990
27        1994             2005
28        1958             early 1970's
29        	
30        1967             1978
31        1998	
32        1910, 1946       1960
33        1969             1976, 1980
34        1920	
35        1984	
36        1960's

Of these, only [tt]risk_num[/tt]'s 1, 2, 4, 5, 20, 22, 26, 27, 30, 31, 34, & 35 are good, 21 & 29 are NO GOOD since they lack a year built.

I'd greatly appreciate any help in this.

Thanks,



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Universal Model

 

[VRoscioli]

You're looking to just display those that are invalid? How about:

SELECT * FROM tblTable
WHERE ri_year_built Is Null
    OR Nz(Val(ri_year_built),0) < 1805
    OR Nz(Val(ri_year_built),0) > 2007
    OR Nz(Val(ri_year_remod),0) < 1805
    OR Nz(Val(ri_year_remod),0) > 2007;

-V

 

[PHV]

Something like this ?
SELECT *
FROM yourTable
WHERE ri_year_built Like '####' AND Val(ri_year_built) Between 1805 And 2007

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

In Access 2000, Val only works properly if the number is the first part of the string.

[tt]Not Like "*####*"[/tt]

Will show up records without 4 digit numbers.

 

[Remou]

I think I would be inclined to mark the completely correct years based, perhaps, on a template table, then mark the completely incorrect years and finally manually examine the borderline cases.

 

[Gooser]

Thanks a bunch for the help.

We have a couple of summer-hires that do data-cleanup, I'm just trying to get them a list of records that don't have good dates to look at.

--Gooser

 

[BSman]

You could also select all records where the length of ri_year_built is not equal to 4, something like this:

SELECT * FROM yourTable WHERE len(ri_year_built) <> 4

This should show all of the invalid records with the exception of any where ri_year_built contains 4 characters that do not correspond to a date.

Bob