String Functions in Access XP

[RCollinge]

Hi.

I hope someone can help because I'm really stuck!

I have a table with three fields.

Field1 contains postcodes
Field2 contains a letter
Field3 contains a number

I need to be able to run a query to sum Field3 by postcode sector and
by letter.

I'm trying to write a string function to break up the postcode so i can
sum by sector level. For example BA11 4BX, needs to be selected as BA11
4. However, some postcodes will only have one letter at the beginning,
(S2 4LE needs to be selected as S2 4).

Basically, what I need to get the string function to do is take the
characters before the space and the number after the space. Sounds easy
in theory but I haven't had any experiance with string functions
before.

I can then create a new Field4 to store the postcode at this level so I
can run reports and queries.

Please help :)

Thanks

Rachel

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

[RCollinge]

[Steve Schapel]

Rachel,

You can use the InStr() function to detect the position of the space in
the postcode. So, to extract the information you want, the expression
would be...
Left([Field1],InStr([Field1]," ")+1)

However, you are incorrect in suggesting this value gets stored in a
fourth field. The expression should be used directly in a calculated
field in your query in order to get your sums, counts, etc, and to
provide the data for your report.

--
Steve Schapel, Microsoft Access MVP

[RCollinge]

Thank you. I managed to use the function within my update query to
select out the bits I needed for my new field. :)

[RCollinge]

Thank you. I managed to use the function within my update query to
select out the bits I needed for my new field. :)