Message #1 by topwebdesigns@h... on Tue, 11 Jun 2002 20:09:50
```I want to know if it posibble in access to take a field that has a
standard layout of field name YEAR" 91-94" the numbers vary,(other lines
have single "91" digits is there a way to say if this exists (two digits-
two digits) then create the second colmun to have the year ending value```
 Message #2 by "Gregory Serrano" on Wed, 12 Jun 2002 17:57:48
```<< I want to know if it posibble in access to take a field that has a
standard layout of field name YEAR" 91-94" the numbers vary,(other lines
have single "91" digits is there a way to say if this exists (two digits-
two digits) then create the second colmun to have the year ending value >>

If this is going to be an issue when you always want to separate the
years, then I strongly suggest that you take all the data, separate it,
and never let anyone enter strings like that again.  Separate the one
string field YEAR into two integers (in case you want to do math, i.e.
subtract them) intStartYear and intEndYear.

Create intStartYear and intEndYear in the same table as YEAR.  Open an
Update Query and specify the three year fields.

First run, split the ranged years into two (e.g. "91-94"):

For YEAR enter the criterion Like "*-*"
For intStartYear enter the "update to" as Left([YEAR],2) + 1900
For intEndYear enter the "update to" as Right([YEAR],2) + 1900

Run the query.

Second run, take non-ranged years (e.g. "91") and make start = end:

For YEAR enter the criterion Not Like "*-*"
For intStartYear enter the "update to" as [YEAR] + 1900
For intEndYear enter the "update to" as [YEAR] + 1900

Run the query.  Decide on a 20th century / 21st century split, say, "05"
means 1905 but "04" means 2004.

Third run, take all 1900s to 1904s and convert them to 2000s to 2004s.

If this works correctly, every entry will have a correct start and end
year.  If so, kill the field YEAR.  Use intStartYear and intEndYear from
now on and change your forms, queries, reports, etc. accordingly.  Make
sure your forms, etc. are programmed to accept years in four digits from
now on.

Greg
```
 Message #3 by "Gregory Serrano" on Wed, 12 Jun 2002 21:38:59
```Oh, note:  to add 1900 to the years you will probably have to do this:

intStartYear = Val(Left([YEAR],2)) + 1900
intEndYear = Val(Right([YEAR],2)) + 1900

intStartYear = Val([YEAR]) + 1900
intEndYear = Val([YEAR]) + 1900

because YEAR is a string and intStartYear and intEndYear are integers.

Greg
```
 Message #4 by "Wesley Kendrick" on Fri, 14 Jun 2002 02:22:07 +0100
```Hi, you need some code to do this. I'm not sure I understand your question
exactly, but

You could try using Len something like this

If  Len(YEAR) =2  Then           'field contains "91"

YEAR =YEAR  +  "-" + YourYearEndingValue

End If

This will have the effect of adding "-XX" to any record that only contains
"91" but not to records containing "XX-XX"
Is that what you mean?

Hope this helps,
Regards, Wesley Kendrick

----- Original Message -----
From: <topwebdesigns@h...>
To: "Access" <access@p...>
Sent: Tuesday, June 11, 2002 8:09 PM
Subject: [access] Splitting info

> I want to know if it posibble in access to take a field that has a
> standard layout of field name YEAR" 91-94" the numbers vary,(other lines
> have single "91" digits is there a way to say if this exists (two digits-
> two digits) then create the second colmun to have the year ending value
>

```
 Message #5 by topwebdesigns@h... on Tue, 18 Jun 2002 23:21:48
```Thank you for your help
```