I am trying to convert values in the numeric field [HW] that are negative
into zeros while keeping positive values unchanged.

when I try the expression IIf([HW]<0,0,[HW]) as a crosstab query criteria
the negative values all come out as 1, while the positive values remain
unchanged.

Does anyone know why I am getting 1 instead of 0 and what to do about it?

update query:

http://office.microsoft.com/en-us/ac...880881033.aspx

make your search criteria "<0"
update value "0"
(omit quotation marks)


--
Chris Hayes
Still a beginner


"Dan" wrote:

> I am trying to convert values in the numeric field [HW] that are negative
> into zeros while keeping positive values unchanged.
>
> when I try the expression IIf([HW]<0,0,[HW]) as a crosstab query criteria
> the negative values all come out as 1, while the positive values remain
> unchanged.
>
> Does anyone know why I am getting 1 instead of 0 and what to do about it?

In design view of your crosstab, what do you have in the Group By row?

Is it Count? That would make sense (counting 1 record.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan" <(E-Mail Removed)> wrote in message
news:7C8C9112-DD16-47AB-95ED-(E-Mail Removed)...
>I am trying to convert values in the numeric field [HW] that are negative
> into zeros while keeping positive values unchanged.
>
> when I try the expression IIf([HW]<0,0,[HW]) as a crosstab query criteria
> the negative values all come out as 1, while the positive values remain
> unchanged.
>
> Does anyone know why I am getting 1 instead of 0 and what to do about it?

Thanks Allen

I just had Group By left there. What should it be?

"Allen Browne" wrote:

> In design view of your crosstab, what do you have in the Group By row?
>
> Is it Count? That would make sense (counting 1 record.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan" <(E-Mail Removed)> wrote in message
> news:7C8C9112-DD16-47AB-95ED-(E-Mail Removed)...
> >I am trying to convert values in the numeric field [HW] that are negative
> > into zeros while keeping positive values unchanged.
> >
> > when I try the expression IIf([HW]<0,0,[HW]) as a crosstab query criteria
> > the negative values all come out as 1, while the positive values remain
> > unchanged.
> >
> > Does anyone know why I am getting 1 instead of 0 and what to do about it?
>
>

Switch the query to SQL View (View menu), and post the SQL statement. It
might help us to see what's going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan" <(E-Mail Removed)> wrote in message
news:4C863F64-3C4B-43E1-B2BC-(E-Mail Removed)...
> Thanks Allen
>
> I just had Group By left there. What should it be?
>
> "Allen Browne" wrote:
>
>> In design view of your crosstab, what do you have in the Group By row?
>>
>> Is it Count? That would make sense (counting 1 record.)
>>
>> "Dan" <(E-Mail Removed)> wrote in message
>> news:7C8C9112-DD16-47AB-95ED-(E-Mail Removed)...
>> >I am trying to convert values in the numeric field [HW] that are
>> >negative
>> > into zeros while keeping positive values unchanged.
>> >
>> > when I try the expression IIf([HW]<0,0,[HW]) as a crosstab query
>> > criteria
>> > the negative values all come out as 1, while the positive values remain
>> > unchanged.
>> >
>> > Does anyone know why I am getting 1 instead of 0 and what to do about
>> > it?