2

I have Excel export within Access 2007. The only thing I am not entirely sure about is I have value that appears as an age, for example "1-3", this obviously becomes higher dependent on customer selection.

I want two IIf statements: one to select the data before the "-" and one for after "-", can anyone help?

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
LENBERT88
  • 21
  • 4
  • 1
    Do you mean you have a column containing "1-3" and you want to match that with the inputs "1", "2" or "3" ? If so a `fldFrom | fldTo` would be better – Alex K. Jan 04 '12 at 13:37
  • Hi, Alex Yes its containing "1-3 and in the export i want that to come in two separte number the number 1 in one coloum and the number 3 in another coloum. – LENBERT88 Jan 04 '12 at 13:39

2 Answers2

2

You could

select
  T.agerange, 
  iif(agerange is null, "", left(agerange,instr(agerange,"-")-1)), 
  iif(agerange is null, "", mid(agerange,instr(agerange,"-")+1))
from T;

however a 2 column design would be far superior.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Whats above should return `1-3, 1, 3`? – Alex K. Jan 04 '12 at 14:12
  • I've updated to allow for nulls, this will work for xxx-xxx formats, if it does not you need to provide more details as to why – Alex K. Jan 04 '12 at 14:30
  • IIf(IsNull([strAge]),"",Left([strAge],InStr(1,[strAge],"-")-1)) – LENBERT88 Jan 04 '12 at 15:03
  • IIf(IsNull([strAge]),"",Mid([strAge],InStr(1,[strAge],"-")+1)) – LENBERT88 Jan 04 '12 at 15:04
  • I would amend the second iif from using a mid to the following "right(agerange,InStrRev(agerange,"-")-1" this will ensure it still works if your age is more than a single digit. +1 still though as this is a great answer. : ) – Matt Donnan Jan 04 '12 at 16:12
0
IIf(IsNull([strAge]),"",Left([strAge],InStr(1,[strAge],"-")-1)) 

IIf(IsNull([strAge]),"",Mid([strAge],InStr(1,[strAge],"-")+1))
DaveShaw
  • 52,123
  • 16
  • 112
  • 141
LENBERT88
  • 21
  • 4