Instring function in Access, extract first two characters

[Lifeseeker]

Hello,

I have a subform built on a main form.

In the subform, there are two columns, A and B. They are store texts.

For ease of data entry, I would like to be able to have Access check the first two characters of a string in column A, and if it is....for example, 02, then in column B I want it to say 2002.

If it is 00 in Column A, then I want column B to display 2000, etc. So if it works, it will make data entry a bit easier.

The texts in column A are also hyperlinked, but this doesn't matter does it?

I'm thinking I should use some kind of InStr function but not sure.


any thought/comment is much appreciated.

 

[Mr. B]

If you are using a query or sql statement as the record source for your subform you can add an expression in your query that will do what you want.

That expression would be something like:

ColB: "20" & Left(ColA,2)

Just copy and past the line above in the Field row of an empty column of your query, changing the "ColA" to the actual name of the field that you describe as column A.

This line of code first provides a name for the column: "ColB:" You can change this to be anything you want it to be.

It next assigns the "20" as a string in front of the results of the "Left(ColA,2)" part.

The "Left(ColA,2)" part just uses the Left Function to return the first 2 characters at the left end of the value stored in Column A.

Hope this helps.

 

[Beetle]

You could do this as an expression, or calculated field, in a query but you should not attempt to store the derived value in a table field. The expression might look like;

MyValue:"20" & Left([ColumnAField],2)