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 an
by letter.

I'm trying to write a string function to break up the postcode so i ca
sum by sector level. For example BA11 4BX, needs to be selected as BA1
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 th
characters before the space and the number after the space. Sounds eas
in theory but I haven't had any experiance with string function
before.

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

Please help

Thanks

Rache

 

[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.

 

[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 t
select out the bits I needed for my new field. :