if query

[travismp]

SampleID2: IIf(Left([SampleID],1)<=4,"DOT","Non-DOT")

here is my query which works great almost. If the field is blank then it will get a "DOT" status. Can I change something here to not add anything for a null value? thanks

 

[raskew]

Hello,

here is my query?

Bob












































o

 

[travismp]

I do not understand if this is sarcasim... maybe I was not clear.

I have a field called SampleID. A 7 digit number will be in the column most of the time. If the number starts with a 1,2,3,4 I want a column [SampleID2] to say "DOT" and if the 7 digit number statrs with 5,6,7,8 it will display "Non-DOT" all works great.

however I have a report and if the field is blank then it will display "DOT" as if somehow thinking blank=0 which is less than 4. I do not know of another reason why it would say "DOT" on a blank field.


SampleID2: IIf(Left([SampleID],1)<=4,"DOT","Non-DOT")

 

[mousie]

I do not understand if this is sarcasim... maybe I was not clear.

I have a field called SampleID. A 7 digit number will be in the column most of the time. If the number starts with a 1,2,3,4 I want a column [SampleID2] to say "DOT" and if the 7 digit number statrs with 5,6,7,8 it will display "Non-DOT" all works great.

however I have a report and if the field is blank then it will display "DOT" as if somehow thinking blank=0 which is less than 4. I do not know of another reason why it would say "DOT" on a blank field.


SampleID2: IIf(Left([SampleID],1)<=4,"DOT","Non-DOT")

SampleID2: Iif([SampleID] is null,"EMPTY",IIf(Left([SampleID],1)<=4,"DOT","Non-DOT"))

should do it!

 

[Moniker]

Even easier:

SampleID2: Iif([SampleID] is null,"EMPTY",IIf(Left([SampleID],1)<=4,"DOT","Non-DOT"))

SampleID2 = Switch(Nz([SampleID],"")="","EMPTY",Left([SampleID],1)<=4,"DOT",True,"Non-DOT")