Convert Negative values to zero

[Majic]

I need to be able to convert negative numbers ro zero in query. How
do I do that?

Thank you

 

[BruceM]

First, understand that this is a volunteer newsgroup. You should wait 24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth (use the
actual name for your field instead of NumberField, and choose the name you
like for NewField).

 

[Majic]

First, understand that this is a volunteer newsgroup. You should wait 24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth (use the
actual name for your field instead of NumberField, and choose the name you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?

Thank you- Hide quoted text -

- Show quoted text -

Thank you Bruce. I did not mean to post it more than once. Everytime
I posted it, it gave me an error message that I could not post at this
time. So, I kept trying it.
I appreciate your time and effort and help on this.

 

[Majic]

First, understand that this is a volunteer newsgroup. You should wait 24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth (use the
actual name for your field instead of NumberField, and choose the name you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?

Thank you- Hide quoted text -

- Show quoted text -

Bruce,
Thank you your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

 

[BruceM]

First, understand that this is a volunteer newsgroup. You should wait 24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth (use the
actual name for your field instead of NumberField, and choose the name
you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?

Thank you- Hide quoted text -

- Show quoted text -

Bruce,
Thank you your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

I can't see your query, so I don't know exactly what is contained in
Downtime or any other field. Also, if you receive an error message, it is
best to provide details.
If downtime is a negative number, you could adapt the expression I provided
something like this:
Downtime: IIf([NumberField] > 0,0,[NumberField])
This will give you negative numbers, and 0 in place of positive numbers. A
totals query would let you add the results of the expression. Help has more
information about a totals query.
When asking about a query it is often best to post its SQL. Click View >
SQL View. Copy what you see, and paste it into a message, along with a
description of what the database is for and what the query is meant to
accomplish. In your case you have already explained what you need the query
to do, but a concise explanation will save people the need to search the
rest of the thread.

 

[Majic]

First, understand that this is a volunteer newsgroup. You should wait 24
hours before posting again. Three postings in five minutes is not
appropriate.
At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])
Use NewField instead of NumberField in calculations and so forth (use the
actual name for your field instead of NumberField, and choose the name
you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?
Thank you- Hide quoted text -
- Show quoted text -

Bruce,
Thank you your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

I can't see your query, so I don't know exactly what is contained in
Downtime or any other field. Also, if you receive an error message, it is
best to provide details.
If downtime is a negative number, you could adapt the expression I provided
something like this:
Downtime: IIf([NumberField] > 0,0,[NumberField])
This will give you negative numbers, and 0 in place of positive numbers. A
totals query would let you add the results of the expression. Help has more
information about a totals query.
When asking about a query it is often best to post its SQL. Click View >
SQL View. Copy what you see, and paste it into a message, along with a
description of what the database is for and what the query is meant to
accomplish. In your case you have already explained what you need the query
to do, but a concise explanation will save people the need to search the
rest of the thread.- Hide quoted text -

- Show quoted text -

Bruce,
Thank you for your quick response. Here is the SQL
SELECT TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total, TimeSheet.TaktTime,
([Total]-[TaktTime]) AS TotalHours, IIf([TotalHours]<0,0,[TotalHours])
AS Downtime
FROM [Time Cards] RIGHT JOIN TimeSheet ON [Time Cards].EmployeeID =
TimeSheet.OperatorID
GROUP BY TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
TimeSheet.TaktTime, IIf([TotalHours]<0,0,[TotalHours]);

So the value that I would like to have a total of is Downtime, which
is after changing negative to 0 from TotalHours value.

Thank you so much

 

[BruceM]

I just realized there are parallel questions and answers in another thread.
I will leave the rest of this discussion for that thread. If this happens
in the future (because you accidentally posted twice or whatever), direct
everybody to one thread.

First, understand that this is a volunteer newsgroup. You should wait
24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth (use
the
actual name for your field instead of NumberField, and choose the name
you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?

Thank you- Hide quoted text -

- Show quoted text -

Bruce,
Thank you your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

I can't see your query, so I don't know exactly what is contained in
Downtime or any other field. Also, if you receive an error message, it
is
best to provide details.
If downtime is a negative number, you could adapt the expression I
provided
something like this:
Downtime: IIf([NumberField] > 0,0,[NumberField])
This will give you negative numbers, and 0 in place of positive numbers.
A
totals query would let you add the results of the expression. Help has
more
information about a totals query.
When asking about a query it is often best to post its SQL. Click View >
SQL View. Copy what you see, and paste it into a message, along with a
description of what the database is for and what the query is meant to
accomplish. In your case you have already explained what you need the
query
to do, but a concise explanation will save people the need to search the
rest of the thread.- Hide quoted text -

- Show quoted text -

Bruce,
Thank you for your quick response. Here is the SQL
SELECT TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total, TimeSheet.TaktTime,
([Total]-[TaktTime]) AS TotalHours, IIf([TotalHours]<0,0,[TotalHours])
AS Downtime
FROM [Time Cards] RIGHT JOIN TimeSheet ON [Time Cards].EmployeeID =
TimeSheet.OperatorID
GROUP BY TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
TimeSheet.TaktTime, IIf([TotalHours]<0,0,[TotalHours]);

So the value that I would like to have a total of is Downtime, which
is after changing negative to 0 from TotalHours value.

Thank you so much

 

[Majic]

I just realized there are parallel questions and answers in another thread.
I will leave the rest of this discussion for that thread. If this happens
in the future (because you accidentally posted twice or whatever), direct
everybody to one thread.

First, understand that this is a volunteer newsgroup. You should wait
24
hours before posting again. Three postings in five minutes is not
appropriate.
At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])
Use NewField instead of NumberField in calculations and so forth (use
the
actual name for your field instead of NumberField, and choose the name
you
like for NewField).

I need to be able to convert negative numbers ro zero in query. How
do I do that?
Thank you- Hide quoted text -
- Show quoted text -
Bruce,
Thank you your solution was great. Now, the challange becomes when I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?
Thank you for all your help
I can't see your query, so I don't know exactly what is contained in
Downtime or any other field. Also, if you receive an error message, it
is
best to provide details.
If downtime is a negative number, you could adapt the expression I
provided
something like this:
Downtime: IIf([NumberField] > 0,0,[NumberField])
This will give you negative numbers, and 0 in place of positive numbers.
A
totals query would let you add the results of the expression. Help has
more
information about a totals query.
When asking about a query it is often best to post its SQL. Click View >
SQL View. Copy what you see, and paste it into a message, along with a
description of what the database is for and what the query is meant to
accomplish. In your case you have already explained what you need the
query
to do, but a concise explanation will save people the need to search the
rest of the thread.- Hide quoted text -
- Show quoted text -

Bruce,
Thank you for your quick response. Here is the SQL
SELECT TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total, TimeSheet.TaktTime,
([Total]-[TaktTime]) AS TotalHours, IIf([TotalHours]<0,0,[TotalHours])
AS Downtime
FROM [Time Cards] RIGHT JOIN TimeSheet ON [Time Cards].EmployeeID =
TimeSheet.OperatorID
GROUP BY TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
TimeSheet.TaktTime, IIf([TotalHours]<0,0,[TotalHours]);

So the value that I would like to have a total of is Downtime, which
is after changing negative to 0 from TotalHours value.

Thank you so much- Hide quoted text -

- Show quoted text -

I am new at this and forgive me if I miss communicate. Could you
please help me on this, this time? Next time I will try to do it the
right way.

Thank you

 

[BruceM]

See my reply in the thread to which John Vinson contributed. He knows much,
much more than I, so I wanted to see if he had a response.

I just realized there are parallel questions and answers in another
thread.
I will leave the rest of this discussion for that thread. If this
happens
in the future (because you accidentally posted twice or whatever), direct
everybody to one thread.

First, understand that this is a volunteer newsgroup. You should
wait
24
hours before posting again. Three postings in five minutes is not
appropriate.

At the top of a blank column in query design view:
NewField: IIf([NumberField] < 0,0,[NumberField])

Use NewField instead of NumberField in calculations and so forth
(use
the
actual name for your field instead of NumberField, and choose the
name
you
like for NewField).

I need to be able to convert negative numbers ro zero in query.
How
do I do that?

Thank you- Hide quoted text -

- Show quoted text -

Bruce,
Thank you your solution was great. Now, the challange becomes when
I
am adding total Downtime.
First name in Query "TotalHours" was including negative and positive
numbers. Of course, I can not add those because it will give an
inaccurate results.
Second name in query is Downtime and I used IIf statement you gave
me
in a query. Ideally will be to total Downtime and not TotalHours
(downtime with negative and positive numbers). I created a report
for
Downtime and I tried to total Downtime by TotalDowntime:
Sum([Downtime]) in query and it gave me an error.
Could you please direct me on what I need to do?

Thank you for all your help

I can't see your query, so I don't know exactly what is contained in
Downtime or any other field. Also, if you receive an error message,
it
is
best to provide details.
If downtime is a negative number, you could adapt the expression I
provided
something like this:
Downtime: IIf([NumberField] > 0,0,[NumberField])
This will give you negative numbers, and 0 in place of positive
numbers.
A
totals query would let you add the results of the expression. Help
has
more
information about a totals query.
When asking about a query it is often best to post its SQL. Click
View >
SQL View. Copy what you see, and paste it into a message, along with
a
description of what the database is for and what the query is meant to
accomplish. In your case you have already explained what you need the
query
to do, but a concise explanation will save people the need to search
the
rest of the thread.- Hide quoted text -

- Show quoted text -

Bruce,
Thank you for your quick response. Here is the SQL
SELECT TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
HoursAndMinutes([TimeOut]-[TimeIn]) AS Total, TimeSheet.TaktTime,
([Total]-[TaktTime]) AS TotalHours, IIf([TotalHours]<0,0,[TotalHours])
AS Downtime
FROM [Time Cards] RIGHT JOIN TimeSheet ON [Time Cards].EmployeeID =
TimeSheet.OperatorID
GROUP BY TimeSheet.OperatorID, TimeSheet.omo, TimeSheet.StationID,
TimeSheet.[Date Tested], TimeSheet.TimeIn, TimeSheet.TimeOut,
TimeSheet.TaktTime, IIf([TotalHours]<0,0,[TotalHours]);

So the value that I would like to have a total of is Downtime, which
is after changing negative to 0 from TotalHours value.

Thank you so much- Hide quoted text -

- Show quoted text -

I am new at this and forgive me if I miss communicate. Could you
please help me on this, this time? Next time I will try to do it the
right way.

Thank you