MS Access: Creating a new field using the first character of another

Can this be done?

[MarkJH]

I have a table called 'RECORD LABELS' which has a field called 'Label'. I'd like a query which would create a new field (called 'Alphanumeric') just containing the first character of the records in the field 'Label'.

On top of this, when the record in the field 'Label' starts with a number, I want the record in the corresponding 'Alphanumeric' field to say "0-9", instead of the first character.

Is this possible and how would I implement it?

Thanks!

[webdevjim]

Hey,
You'll have to look around for the exact code but here's how to do it:

- Grab the LABEL field data
- Make a variable that's a substring of the LABEL field lets call it LABELSUBSTRING
- Check to see if the LABELSUBSTRING isNumeric
if it's a number print 0-9 to the ALPHANUM field
else print alpha to the ALPHANUM field

I hope that helps.

[WebJoe]

UPDATE [RECORD LABELS] SET [Alphanumeric] = IIf(Asc(Left([Label],1))<58 And Asc(Left([Label],1))>47,'0-9',Left([Label],1))

all labels with numbers will have the string '0-9' in the field [Alphanumeric], all others (including special charaters) will have the first character

> ADD: no variables needed...

[MarkJH]

Thanks guys! :)