Converting negative numbers into a zero

[Guest]

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?

 

[Guest]

update query:

http://office.microsoft.com/en-us/access/HP051880881033.aspx

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

 

[Allen Browne]

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.)

 

[Guest]

Thanks Allen

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen

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

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.)

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?