separating data before and after a hyphen.

[bird lover]

work in access 2003, no programming skills.

Field [Claim] has multiple records with numbers before and after hyphen,
i.e.
"2007-12345" or "2008-2" or "2005-1234567", I want to update these records
into
[claimyear] and [claimno] so it looks like this:
2007 12345
2008 2
2005 1234567

Syntax for an update query would be greatly appreciated. I think, but not
sure it has something to do with four numbers which always precede the
hyphen, or looking for hyphen and then splitting data before and after.
These are musings only.

 

[Bob Barrows]

work in access 2003, no programming skills.

Field [Claim] has multiple records with numbers before and after
hyphen, i.e.
"2007-12345" or "2008-2" or "2005-1234567", I want to update these
records into
[claimyear] and [claimno] so it looks like this:
2007 12345
2008 2
2005 1234567

Syntax for an update query would be greatly appreciated. I think,
but not sure it has something to do with four numbers which always
precede the hyphen, or looking for hyphen and then splitting data
before and after. These are musings only.

The year part is easy:
Set claimyear=left(claim,4),

The number part is a little harder, but can be accomplished using
InStr() and Mid():

claimno=Mid(claim,Instr(claim,"-") + 1)

 

[mscertified]

before hyphen:
LEFT(Claim,4)
after hyphen:
MID(Claim,6)

-Dorian

 

[Bob Barrows]

The number part is a little harder, but can be accomplished using
InStr() and Mid():

claimno=Mid(claim,Instr(claim,"-") + 1)

or
claimno=Mid(claim,6)

<blush>