Help on IIf

[SteveOll]

Hi All

I'm sure this is really simple but I'm new to Access and can't find anything simalar already in the furum, probably because it is so easy.

I have a make table query that has a Colunm called Days Open as below . I want to extract the number from the field and loose everything else, I'm not interested in anything < than 1 day so I already have ...... ..............

Duration: IIf(Left([Days Open],1)="<","1") ?????????????????????

but what code do I need to get the other numbers

Days Open
< 1 day
1 day
2 days
5 days
< 1 day

If there is a better way than what I'm using then please help.

Thanks in advance

Steve

 

[Dennisk]

you could try the VAL function although that stops reading at the first non numeric value so you would need to determine where the numeris start.
OR
write a functions that reads each chr and if it is numeris then store it.
upon completion then use the val function

 

[SteveOll]

I looked at the VAL function but the numbers don't appear in the same place every time. Can I use the IIF statement to delete the < then I could just use the left, 1 to read the number?

Steve

 

[Dennisk]

you can do, but by making a generic function even if at a later date you want to parse >= 1 day then you do not need to amend anything.

Public Function ParseString(strNumber as string) as Integer
dim i as integer
dim strChar as string * 1
dim strResult as string

for i = len(strnumber)
strchar=Mid$(strNumber ,i,1)
if isNumeric(strChar) then
strResult=strResult & strChar
endif
next

ParseString=Cint(strResult)
End Function

 

[aymanmb]

I am not an expert yet in Access, buf feel that the Select Case is the right one for this issue. I am not fiamiliar however how to set it properly.

regards

 

[Brianwarnock]

I think you need a where clause in the query such as

WHERE (((IIf(Left([days open],1)<>"<",True))=True));

and use Val([days open]) to extract the number when the record is selected.

eg

SELECT Val([days open]) AS Expr2
FROM yourtable
WHERE (((IIf(Left([days open],1)<>"<",True))=True));

plus other fields of course,
BTW it is better not to have spaces in object names in ACCESS as they teng to leed to syntax errors.

brian

 

[Moniker]

Use a switch.

[days open] = Switch(Left([days open],1)<>"<",[days open],True,Null)

In that field's criteria, put Is Not Null.