Re: Blank Space to Zero

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 11:39:53 -0500

This kinda depends on what you mean by "blank space".
If you mean Null, then Nz([YourField],0) should work.
If you mean a space, then IIf([YourField]=" ",0,[YourField])
If you mean a zero length string, then IIf([YourField]="",0,[YourField])
or to cover all bases:
If you mean a space, then IIf(Trim([YourField] & "") = "",0,[YourField]) 

-- 
Duane Hookom
MS Access MVP
--
"Chris" <Chris@discussions.microsoft.com> wrote in message
news:EBB1FE95-BC16-44DB-ACE4-AAA18111CDB9@microsoft.com...
> I have read many posts on converting a blank space to zero but cannot seem
to
> make it work.  Here is the sql statement from my query, any suggestions.
>
> SELECT qselKCCountYes.txtPlant, tblQuestions.QstnLvl1, qselKCCountYes.[Yes
> Responses], qselKCCountNo.[No Responses], [qselKCCountN-A].[N/A Responses]
> FROM [qselKCCountN-A] RIGHT JOIN (qselKCCountNo RIGHT JOIN (qselKCCountYes
> RIGHT JOIN tblQuestions ON qselKCCountYes.[Main Section Number] =
> tblQuestions.QstnLvl1) ON qselKCCountNo.[Main Section Number] =
> tblQuestions.QstnLvl1) ON [qselKCCountN-A].[Main Section Number] =
> tblQuestions.QstnLvl1
> GROUP BY qselKCCountYes.txtPlant, tblQuestions.QstnLvl1,
qselKCCountYes.[Yes
> Responses], qselKCCountNo.[No Responses], [qselKCCountN-A].[N/A Responses]
> ORDER BY tblQuestions.QstnLvl1;
>
> Thanks

Relevant Pages