separating data before and after a hyphen.

  • Thread starter Thread starter bird lover
  • Start date Start date
B

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.
 
bird said:
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.
Click to expand...

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)
 
Bob said:
The number part is a little harder, but can be accomplished using
InStr() and Mid():

claimno=Mid(claim,Instr(claim,"-") + 1)
Click to expand...

or
claimno=Mid(claim,6)

<blush>
 
Back
Top