Re: IsEmpty() not giving expected results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"SRussell" <srussell@xxxxxxxxxxx> wrote in message
news:%23mq4tez4GHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Fixup problem with pTAL program
    ... This fix from KBNS looks like it'll do the trick! ... TitlepTAL program reports unresolved external reference - ... Look in the source for the use of an empty string in a move statement ...
    (comp.sys.tandem)
  • Re: sending variable to include file
    ... problem I can't fix, ... being nothing but an empty string. ... You can't add ?args to an include; PHP doesn't evalutate that when ... that's the HTTP daemon's job.) ...
    (comp.lang.php)
  • Re: Missing KB article on Framework hotfix
    ... Call the MS Support Services of your country, tell them to search in Google ... FIX: The Server property may return as an empty string when you receive the ...
    (microsoft.public.dotnet.framework)
  • Re: sending variable to include file
    ... vetchling wrote: ... problem I can't fix, ... being nothing but an empty string. ... You don't need to pass a variable to an included file, it already has access to all variables in the includING script. ...
    (comp.lang.php)
  • Re: IsEmpty() not giving expected results
    ... SRussell wrote: ... non normalized and I'm not paid to fix that:(So I am trying to get the count of shirts in this case. ...
    (microsoft.public.access.queries)