Pull out year from a string

[tpeter]

I have a Field that contains a goup of numbers, with the first two numbers
being the data code. An example of the group of numbers is:
04E153528 (this would be 2004)
99Z798858 (this would be 1999)

Is it best to use an iff statmetent in a query for this, or something
different?
this data is going to be dumped into excel when it is finished, so is it
better to do this in Access or Excel?

When I do it in Excel I used the stament of
if(left(b2,2)<11,19&left(b2,2),20&left(b2,2)). This kind of works but I don't
think it is recognizing the first 2 as numbers.

Thank you for your help.

 

[pietlinden]

I have a Field that contains a goup of numbers, with the first two numbers
being the data code. An example of the group of numbers is:
04E153528 (this would be 2004)
99Z798858 (this would be 1999)

Is it best to use an iff statmetent in a query for this, or something
different?
this data is going to be dumped into excel when it is finished, so is it
better to do this in Access or Excel?

When I do it in Excel I used the stament of
if(left(b2,2)<11,19&left(b2,2),20&left(b2,2)). This kind of works but I don't
think it is recognizing the first 2 as numbers.

Thank you for your help.

you have to convert it to a number for it to be recognized that way.
Since the field is text, it is still being treated as a string.


IIF(CInt(left(b2,2))<11,....

 

[tpeter]

you have to convert it to a number for it to be recognized that way.
Since the field is text, it is still being treated as a string.


IIF(CInt(left(b2,2))<11,....

I have put the following change in and now I get a NAME? error, (in Excel)
=IF(cInt(LEFT(C2,2))<11,19&cInt(LEFT(C2,2)),20&cInt(LEFT(C2,2)))

I have also tried reformatting the cells to a number but this doen't seem to
work either.

 

[John W. Vinson]

I have a Field that contains a goup of numbers, with the first two numbers
being the data code. An example of the group of numbers is:
04E153528 (this would be 2004)
99Z798858 (this would be 1999)

Is it best to use an iff statmetent in a query for this, or something
different?
this data is going to be dumped into excel when it is finished, so is it
better to do this in Access or Excel?

When I do it in Excel I used the stament of
if(left(b2,2)<11,19&left(b2,2),20&left(b2,2)). This kind of works but I don't
think it is recognizing the first 2 as numbers.

Thank you for your help.

Try:

IIf(IsNumeric(Left([b2], 2), Year(DateSerial(Left([b2], 2), 1, 1), NULL)

 

[tpeter]

I have a Field that contains a goup of numbers, with the first two numbers
being the data code. An example of the group of numbers is:
04E153528 (this would be 2004)
99Z798858 (this would be 1999)

Is it best to use an iff statmetent in a query for this, or something
different?
this data is going to be dumped into excel when it is finished, so is it
better to do this in Access or Excel?

When I do it in Excel I used the stament of
if(left(b2,2)<11,19&left(b2,2),20&left(b2,2)). This kind of works but I don't
think it is recognizing the first 2 as numbers.

Thank you for your help.

Try:

IIf(IsNumeric(Left([b2], 2), Year(DateSerial(Left([b2], 2), 1, 1), NULL)

Thank you for your help, I am sorry to bother you so much.

 

[tpeter]

I have a Field that contains a goup of numbers, with the first two numbers
being the data code. An example of the group of numbers is:
04E153528 (this would be 2004)
99Z798858 (this would be 1999)

Is it best to use an iff statmetent in a query for this, or something
different?
this data is going to be dumped into excel when it is finished, so is it
better to do this in Access or Excel?

When I do it in Excel I used the stament of
if(left(b2,2)<11,19&left(b2,2),20&left(b2,2)). This kind of works but I don't
think it is recognizing the first 2 as numbers.

Thank you for your help.

Try:

IIf(IsNumeric(Left([b2], 2), Year(DateSerial(Left([b2], 2), 1, 1), NULL)

Thank you for your help, I am sorry to bother you so much.

here is the correct syntax for the excel side:

=IF(LEFT(B2,2)*1<11,2000+LEFT(B2,2),1900+LEFT(B2,2))