p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Splitting info


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" <SerranoG@m...> 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" <SerranoG@m...> 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" <wez.k@n...> 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

  Return to Index