IsEmpty() not giving expected results

S

SRussell

I am trying to get a count of how many columns have data entered. This is
non normalized and I'm not paid to fix that :( So I am trying to get the
count of shirts in this case.

Alterations.Date, IIf([Shirt 1]=" ",0,1)+IIf([Shirt 2]="
",0,1)+IIf([Shirt 3]=" ",0,1)+IIf([Shirt 4]=" ",0,1) AS ShirtTotal,
IIf(IsEmpty([Shirt 1]),0,1)+IIf(isEmpty([Shirt 2]),0,1)+IIf(isEmpty([Shirt
3]),0,1)+IIf(isEmpty([Shirt 4]),0,1) AS ShirtTotal2,

column is char(50) for all of the shirts.

how do I get a count of this, I guess that I am close?

TIA

__Stephen
 
P

Phil Smith

What if it is equal to " " or " " or " " or " " instead?

I would skip the IsEmpty, and just change

IIf([Shirt 1]=" ",0,1)

to
IIf([Shirt 1]<=" ",0,1)

or better yet
IIf((trim([Shirt 4])="",0,1)

Phil
 
S

SRussell

Phil Smith said:
What if it is equal to " " or " " or " " or " " instead?

I would skip the IsEmpty, and just change

IIf([Shirt 1]=" ",0,1)

to
IIf([Shirt 1]<=" ",0,1)

or better yet
IIf((trim([Shirt 4])="",0,1)

Phil
Click to expand...

Still giving incorrect #s. the code cannot verify the difference between "
" and trim()="" . I also tried the isNull() same poor output.

code:
IIf(trim([Shirt 1])="",0,1) as sh1,
IIf(trim([Trouser 1])="",0,1) as tr1,
IIf([Shirt 1]<=" ",0,1) as sh2,
IIf([Trouser 1]<=" ",0,1) as tr2,


scr_testQ sh1 tr1 sh2 tr2
1 1 1 1
1 1 1 1


scr_testQ Shirt 1 Shirt 2 Shirt 3 Shirt 4 Trouser 1 Trouser 2 Trouser 3
Trouser 4




TAN







KHAKI BLUE/STP NAVY




Is it me or is the Access Engine not up to this? I should see SRn = 0 and
TRn = 1. Why both show a 1 I don't understand.

__Stephen
 
P

Phil Smith

I think we need to know exactly what kind of data we are really dealing
with.

Try running a select query with

len([shirt 1]) and len(trim([shirt 1)] and scan it for blanks. What are
you seeing?

Also, give me a couple examples of valid data to be found in these
fields. Do they all start with alphanumerics?




What if it is equal to " " or " " or " " or " " instead?

I would skip the IsEmpty, and just change

IIf([Shirt 1]=" ",0,1)

to
IIf([Shirt 1]<=" ",0,1)

or better yet
IIf((trim([Shirt 4])="",0,1)

Phil
Click to expand...


Still giving incorrect #s. the code cannot verify the difference between "
" and trim()="" . I also tried the isNull() same poor output.

code:
IIf(trim([Shirt 1])="",0,1) as sh1,
IIf(trim([Trouser 1])="",0,1) as tr1,
IIf([Shirt 1]<=" ",0,1) as sh2,
IIf([Trouser 1]<=" ",0,1) as tr2,


scr_testQ sh1 tr1 sh2 tr2
1 1 1 1
1 1 1 1


scr_testQ Shirt 1 Shirt 2 Shirt 3 Shirt 4 Trouser 1 Trouser 2 Trouser 3
Trouser 4




TAN







KHAKI BLUE/STP NAVY




Is it me or is the Access Engine not up to this? I should see SRn = 0 and
TRn = 1. Why both show a 1 I don't understand.

__Stephen
Click to expand...
 
S

SRussell

Phil Smith said:
I think we need to know exactly what kind of data we are really dealing
with.

Try running a select query with

len([shirt 1]) and len(trim([shirt 1)] and scan it for blanks. What are
you seeing?

Also, give me a couple examples of valid data to be found in these fields.
Do they all start with alphanumerics?
Click to expand...

Code:
IIf([Shirt 1]=" ",0,1)+IIf([Shirt 2]=" ",0,1)+IIf([Shirt 3]="
",0,1)+IIf([Shirt 4]=" ",0,1) AS ShirtTotal,

IIf(IsEmpty([Shirt 1]),0,1)+IIf(isEmpty([Shirt 2]),0,1)+IIf(isEmpty([Shirt
3]),0,1)+IIf(isEmpty([Shirt 4]),0,1) AS ShirtTotal2,
iif(isNull([Shirt 1]) ,0,1) as Shirttotal3,
iif(isNull([Trouser 1]) ,0,1)+iif(isNull([Trouser 1])
,0,2)+iif(isNull([Trouser 3]) ,0,1)+iif(isNull([Trouser 4]) ,0,1) as
Trtotal4,
IIf(len(trim([Shirt 1]))=0,0,1) as sh1,
IIf(len(trim([Trouser 1]))=0,0,1) as tr1,
IIf([Shirt 1]<=" ",0,1) as sh2,
IIf([Trouser 1]<=" ",0,1) as tr2,

len(trim([Shirt 1])) as sh01,
len(trim([Trouser 1])) as tr01,

IIf([Shirt 1]="",0,1)+IIf([Shirt 2]
="",0,1)+IIf([Shirt 3]="",0,1)+IIf([Shirt 4]="",0,1) AS ShirtTotal_1,
IIf([Trouser 1]="",0,1)+IIf([Trouser 2]
="",0,1)+IIf([Trouser 3]="",0,1)+IIf([Trouser 4]="",0,1) AS ShirtTotal_2,


Query output:

scr_testQ ShirtTotal ShirtTotal2 Shirttotal3 Trtotal4 sh1 tr1 sh2 tr2 sh01
tr01
4 4 0 3 1 1 1 1
3
4 4 0 4 1 1 1 1
5


Raw Data:
scr_testQ Shirt 1 Shirt 2 Shirt 3 Shirt 4 Trouser 1 Trouser 2 Trouser 3
Trouser 4




TAN







KHAKI BLUE/STP NAVY




From the data schema:
Well you can't copy that.

Required = No. text, Char(50) in my mind.
 
D

Dale Fye

Stephen,

The best way I've figured to do what you are doing is something like:

Alterations.Date,
IIF(TRIM(NZ([Shirt 1], "")) = "", 0, 1) _
+ IIF(TRIM(NZ([Shirt 2], "")) = "", 0, 1) _
+ IIF(TRIM(NZ([Shirt 3], "")) = "", 0, 1) )
+ IIF(TRIM(NZ([Shirt 4], "")) = "", 0, 1) as Shirt Total,

The NZ([Shirt 1], "") will return the value in [Shirt 1] if it is not null,
and an empty string if it is null. Also, you cannot just use TRIM([Shirt
1]) if you don't know whether that value is NULL, because TRIM(NULL) will
return an error.

Trimming the result of the NZ( ) operation will strip all the spaces from
the result, so if the field is null, or has a zero length string, or has
nothing but spaces in the string, it will return an empty string. Test that
value against an empty string ("") and set the value appropriately

HTH
Dale
 
G

Gary Walter

From the data schema:
Well you can't copy that.

Required = No. text, Char(50) in my mind.
Click to expand...
that's an old James Taylor song isn't it?

"Gone to Char(50) in My Mind"

just to be clear...

if Shirt1 is NULL,

TRIM(Shirt1) = NULL

so, expression

TRIM(Shirt1) = "" is equal to NULL

so,

IIF(TRIM(Shirt1)="",0,1)

is same as

IIF(NULL, 0,1)

whenever the expression in an IIF()
evaluates to NULL, IIF() treats NULL
as "NOT TRUE" so returns the value
as if expression had been FALSE

so, IIF(NULL, 0,1) will return 1 (not 0)

Dale's NZ will workaround this logic problem,
or, another common method:

IIF(TRIM(Shirt1 & "")="",0,1)

where, if Shirt1 is null
(or a zero-length string,
or a string with only SPACES),
the IIF() will return 0

Truth table for above IIF:

Shirt1 IIF(TRIM(Shirt1 & "")="",0,1)
------- --------------------------------
NULL 0
"" 0
" " 0
" " 0
"a" 1
"a " 1

another alternative is

IIF(LEN(TRIM(Shirt1 & ""))=0, 0, 1)

or

IIF(LEN(TRIM(Shirt1 & ""))>0, 1, 0)
 
D

Dale Fye

Gary,

Trim(NULL) will return an error.

Dale

Gary Walter said:
that's an old James Taylor song isn't it?

"Gone to Char(50) in My Mind"

just to be clear...

if Shirt1 is NULL,

TRIM(Shirt1) = NULL

so, expression

TRIM(Shirt1) = "" is equal to NULL

so,

IIF(TRIM(Shirt1)="",0,1)

is same as

IIF(NULL, 0,1)

whenever the expression in an IIF()
evaluates to NULL, IIF() treats NULL
as "NOT TRUE" so returns the value
as if expression had been FALSE

so, IIF(NULL, 0,1) will return 1 (not 0)

Dale's NZ will workaround this logic problem,
or, another common method:

IIF(TRIM(Shirt1 & "")="",0,1)

where, if Shirt1 is null
(or a zero-length string,
or a string with only SPACES),
the IIF() will return 0

Truth table for above IIF:

Shirt1 IIF(TRIM(Shirt1 & "")="",0,1)
------- --------------------------------
NULL 0
"" 0
" " 0
" " 0
"a" 1
"a " 1

another alternative is

IIF(LEN(TRIM(Shirt1 & ""))=0, 0, 1)

or

IIF(LEN(TRIM(Shirt1 & ""))>0, 1, 0)
Click to expand...
 
G

Gary Walter

Dale Fye said:
Trim(NULL) will return an error.
Click to expand...

Maybe different in diff versions?

in AccessXP Immediate Window,

astr=null
?TRIM(astr)
Null

I worked out the following cheat sheet
in Access2K:

/////////////////////////////////////////////
Some Functions: ("error" = invalid use of Null)

Len(Null) = Null
Rnd(Null) --> error
CStr(Null) --> error
CDbl(Null) --> error
Cdec(Null) --> error
workaround --
Cdec(Nz(somefield,0))
Val(Null) --> error
workaround --
Val(Nz(somefield,""))
CDate(Null) --> error
Trim(Null) = Null
StrConv(Null,1)= Null
Left(Null,1) = Null
Rnd(Null) --> error
IsNumeric(Null) = False
IsDate(Null) = False
Asc(Null) --> error
Replace(Null,".","",1,-1,1) -->error

InStr(Null, Str1) = Null
InStr(Str1, Null) = Null
InStr(Null, Str1, Str2) if Start is Null, get Error

?datediff("yyyy",null,date())
Null
?datediff("yyyy",null,date())+
Int(format(date(),"mmdd"))<format(null,"mmdd")
Null

////////////////////
 
G

Gary Walter

perhaps a "better" cheat sheet for null:

/////////////////////////////////////////////
Some Functions: ("error" = invalid use of Null)

CDate(Null) --> error
CCur(Null) --> error
CStr(Null) --> error
CDbl(Null) --> error
CDec(Null) --> error
workaround --
CDec(Nz(somefield,0))
Val(Null) --> error
workaround --
Val(Nz(somefield,""))
TimeValue(Null) --> error
DateValue(Null) --> error
Rnd(Null) --> error
Asc(Null) --> error
Replace(Null,".","",1,-1,1) -->error

Len(Null) = Null
Trim(Null) = Null
StrConv(Null,1)= Null
Left(Null,1) = Null
InStr(Null, Str1) = Null
InStr(Str1, Null) = Null
InStr(Null, Str1, Str2) if Start is Null, get Error

IsNumeric(Null) = False
IsDate(Null) = False

Adate=#9/28/2006#
?Format(Adate,"yyyy-mm-dd")
2006-09-28
Adate=null
?Format(Adate,"yyyy-mm-dd")

?Format(Adate,"yyyy-mm-dd")=vbnullstring
True

?datediff("yyyy",null,date())
Null
?datediff("yyyy",null,date())+
Int(format(date(),"mmdd"))<format(null,"mmdd")
Null

////////////////////
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cont for nothing in a char column 7
T-shirt Sizes 2
Excel Excel help please 1
Current On-Hand-Quantity 1
Return value using partial value of a cell. 2
Tme Sensitive Date Calculation 1
Time Sensitive Calculation Question 3
Switch 2

Top