Display only first words of field

[gschimek]

Is there a way to only display the first part of a field's contents in a report?
For example, let's say I had a database keeping track of cars, and the field in question had data like this:

Chevrolet Camaro (1985)
Ford Mustang (1994)
Honda Accord (2001)

Could I take that data, and in my report, only display everything up to the first parenthesis? And I know it would normally be easier just to create a new field with the year in it, but for the sake of simplicity, let's say I don't want to do that.

 

[ColinEssex]

Look up the INSTR function. (Instring)

Col

 

[GregG]

Is there a way to only display the first part of a field's contents in a report?
For example, let's say I had a database keeping track of cars, and the field in question had data like this:

Chevrolet Camaro (1985)
Ford Mustang (1994)
Honda Accord (2001)

Could I take that data, and in my report, only display everything up to the first parenthesis? And I know it would normally be easier just to create a new field with the year in it, but for the sake of simplicity, let's say I don't want to do that.

Do all records have the year surrounded by parens? (2001) etc? If that is completely standard throughout your table, you could print just the car name with a textbox with a source of = left(CarDescField, len(CarDescField) - 7)

which gives you the leftmost characters with a length of the complete field minus the 7 character date. If the fields are not consistent in using the " (YYYY)" on the end, you're going to lose part of the car description.

ooops. After further review. It looks like you were just using cars as an example. Better to use the other person's advice and use Instr:

= left(CarDescField, Instr(CarDescField, " ") - 1)

In this case, Instr finds the position of the first space in the field, subtract 1 and that's used as the length parameter of the "Left" function.

 

[pam0566]

I Have A Questions. I Have A Field That Is 17 Digits, [letters And Numbers] And Would Like To Show The Last 8 On My Report. I Tried To Adapt These, But Its Not Working.. Any Ideas?

 

[MSAccessRookie]

I Have A Questions. I Have A Field That Is 17 Digits, [letters And Numbers] And Would Like To Show The Last 8 On My Report. I Tried To Adapt These, But Its Not Working.. Any Ideas?

Methinks this is a whole new thought,

BUT:

Take a look at the Right() function. It could provide what you are looking for, as long as the field is a text field.

 

[Mike375]

Right([FieldName],8)

You can either do it as new calculated field in a query by entering on the field row in query design view

NewFieldName:Right([FieldName],8)

or enter it into an unbound textbox as =Right([FieldName],8)

That takes the last 8 characters but remember a space is a character.

So from 12345678912345678 you get 12345678 from the right

But from 1234567891 2345678 you would get 2345678 with a leading space.

Search about on Right, Left, Mid in combination with Len, InStr and LTrim. There are heaps of postings on this forum that cover extracting characters from a field.