Cont for nothing in a char column

[SRussell]

I can't seem to get my count right? Below is my query and return data to
show what is really there. Any ideas on how to get the count proper?

SELECT 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,
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
Trtotal1,

scr_testQ ShirtTotal ShirtTotal2 Shirttotal3 Trtotal4
4 4 0 3
4 4 0 4


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




TAN







KHAKI BLUE/STP NAVY




I think that my ShirtTotals should = 0, and Trtotal = 1 and 3

any ideas?

TIA

__Stephen

 

[Guest]

Try this --
SELECT Alterations.Date, IIf([Shirt 1]="",0,1)+IIf([Shirt 2]
="",0,1)+IIf([Shirt 3]="",0,1)+IIf([Shirt 4]="",0,1) AS ShirtTotal,

 

[SRussell]

Try this --
SELECT Alterations.Date, IIf([Shirt 1]="",0,1)+IIf([Shirt 2]
="",0,1)+IIf([Shirt 3]="",0,1)+IIf([Shirt 4]="",0,1) AS ShirtTotal,

Thanks but nope. Not correct.

I did a Len(trim()) for a column and that may bring up some ideas.

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,

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 in same Query.

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




TAN







KHAKI BLUE/STP NAVY



Why will a NULL not count as 0, or convert to a 1 in isnull() ?

I'm sure it's a null value in 99% of the cases and maybe a few oops and a
space in a clean up for no data belongs there.

So is this a bug in the access engine? From SQL Server I'd have this count
via a case isnull cluster F of case statements.

I have also done the isEmpty() but not correct there.

Any ideas?

 

[Dirk Goldgar]

I can't seem to get my count right? Below is my query and return
data to show what is really there. Any ideas on how to get the count
proper?

SELECT 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,
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
Trtotal1,

scr_testQ ShirtTotal ShirtTotal2 Shirttotal3 Trtotal4
4 4 0 3
4 4 0 4


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




TAN







KHAKI BLUE/STP NAVY




I think that my ShirtTotals should = 0, and Trtotal = 1 and 3

any ideas?

TIA

__Stephen

If you want to count all the cases where the field is neither Null, nor
"", nor any number of spaces), then you might use

IIf(Len(Trim([FieldName] & ""))>0, 1, 0)

 

[SRussell]

If you want to count all the cases where the field is neither Null, nor
"", nor any number of spaces), then you might use

IIf(Len(Trim([FieldName] & ""))>0, 1, 0)

Well in the query it doesn't show a 0, so I put the same code into a new
field on the report and it shows a 0.

Thanks.

I'll still go on record that this is a bug in the Access Engine.

 

[Dirk Goldgar]

If you want to count all the cases where the field is neither Null,
nor "", nor any number of spaces), then you might use

IIf(Len(Trim([FieldName] & ""))>0, 1, 0)

Well in the query it doesn't show a 0, so I put the same code into a
new field on the report and it shows a 0.

That sounds odd, and I'd like to see the query in question.

Thanks.

You're welcome.

I'll still go on record that this is a bug in the Access Engine.

I may not understand exactly what you're doing, but I haven't seen
anything so far that looks at all like a bug to me. So far, all I see
is correct, documented behavior.

 

[SRussell]

If you want to count all the cases where the field is neither Null,
nor "", nor any number of spaces), then you might use

IIf(Len(Trim([FieldName] & ""))>0, 1, 0)

Well in the query it doesn't show a 0, so I put the same code into a
new field on the report and it shows a 0.

That sounds odd, and I'd like to see the query in question.

Thanks.

You're welcome.

I'll still go on record that this is a bug in the Access Engine.

I may not understand exactly what you're doing, but I haven't seen
anything so far that looks at all like a bug to me. So far, all I see
is correct, documented behavior.

Ok. Here is the Code, the results and the raw data.

SELECT 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,
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,
IIf(Len(Trim([Shirt 1] & ""))>0, 1, 0)+ IIf(Len(Trim([Shirt 2] & ""))>0, 1,
0)+IIf(Len(Trim([Shirt 3] & ""))>0, 1, 0)+IIf(Len(Trim([Shirt 4] & ""))>0,
1, 0) AS SF1,
IIf(Len(Trim([Trouser 1] & ""))>0, 1, 0)+IIf(Len(Trim([Trouser 2] & ""))>0,
1, 0)+IIf(Len(Trim([Trouser 3] & ""))>0, 1, 0)+IIf(Len(Trim([Trouser 4] &
""))>0, 1, 0) AS TF1,

Results:
scr_testQ ShirtTotal ShirtTotal2 Shirttotal3 Trtotal4 sh1 tr1 sh2 tr2 sh01
tr01 ShirtTotal_1 ShirtTotal_2 SF1 TF1
4 4 0 3 1 1 1 1
3 4 4 0 1
4 4 0 4 1 1 1 1
5 4 4 0 3


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

 

[Dirk Goldgar]

If you want to count all the cases where the field is neither Null,
nor "", nor any number of spaces), then you might use

IIf(Len(Trim([FieldName] & ""))>0, 1, 0)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

Well in the query it doesn't show a 0, so I put the same code into a
new field on the report and it shows a 0.

That sounds odd, and I'd like to see the query in question.

Thanks.

You're welcome.

I'll still go on record that this is a bug in the Access Engine.

I may not understand exactly what you're doing, but I haven't seen
anything so far that looks at all like a bug to me. So far, all I
see is correct, documented behavior.

Ok. Here is the Code, the results and the raw data.

SELECT 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, 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,
IIf(Len(Trim([Shirt 1] & ""))>0, 1, 0)+ IIf(Len(Trim([Shirt 2] &
""))>0, 1, 0)+IIf(Len(Trim([Shirt 3] & ""))>0, 1,
0)+IIf(Len(Trim([Shirt 4] & ""))>0, 1, 0) AS SF1,
IIf(Len(Trim([Trouser 1] & ""))>0, 1, 0)+IIf(Len(Trim([Trouser 2] &
""))>0, 1, 0)+IIf(Len(Trim([Trouser 3] & ""))>0, 1,
0)+IIf(Len(Trim([Trouser 4] & ""))>0, 1, 0) AS TF1,

Results:
scr_testQ ShirtTotal ShirtTotal2 Shirttotal3 Trtotal4 sh1 tr1 sh2
tr2 sh01 tr01 ShirtTotal_1 ShirtTotal_2 SF1 TF1
4 4 0 3 1 1 1 1
3 4 4 0 1
4 4 0 4 1 1 1 1
5 4 4 0 3


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

Sorry, I can't make enough sense of your test data, when presented in
this format, to try to reproduce your results. The fields in your query
that use the expression I suggested look okay to me, so if you're not
getting the proper results in the query, there's something I'm
overlooking.

If you'd like me to look into it further, you may send me a cut-down
copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in
size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
and ".invalid" from the reply address of this message. If that address
isn't visible to you, you can get my address it from my web site, which
is listed in my sig. Do *not* post my real address in the newsgroup --
I don't want to be buried in spam and viruses.