Does anyone know how to take negative values in a field & convert

[pnabby]

them to zero?

I have some values that are zero and some are positive, however I have a few that are negative.

These values were taken from Quickbooks inventory availability and the cashiers have inadvertently allowed negative availability.

I am looking at the average of a bunch of values to determine reorder points and I don't want negative values to come in negative. I want them to be zero.

Any ideas?

Thanks,
Julie

 

[PHV]

Take a look at the IIf function:
IIf([Your value field]<=0, 0, [Your value field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pnabby]

Thanks for the reply, however I inserted this command in the SQL view and I am getting a syntax error. I must not be entering it correctly.

My Table name is Stock Status and the Field name is On Hand.

Here is what I put in:

SELECT [Stock Status].SKU, [Stock Status].[On Hand], [Stock Status].[Sales/Week]
IIf([Stock Status].[On Hand]<=0, 0, [Stock Status].[On Hand])
FROM [Stock Status];

Thanks for your help.

 

[PHV]

You may try this:
SELECT SKU, [On Hand], [Sales/Week], IIf([On Hand]<=0, 0, [On Hand]) AS newOnHand
FROM [Stock Status];
Or, if you prefer the Query design window:
newOnHand: IIf([On Hand]<=0, 0, [On Hand])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Comma...
SELECT [Stock Status].SKU, [Stock Status].[On Hand], [Stock Status].[Sales/Week][COLOR=blue yellow], [/color]
IIf([Stock Status].[On Hand]<=0, 0, [Stock Status].[On Hand])
FROM [Stock Status];

 

[pnabby]

You guys rock!!! Thank you so much!

As you can tell, I don't write SQL code everyday. I am doing some part time work and I am trying to streamline the reorder process for a fish store.

I guess I know enough to be dangerous...lol

Thanks again!!!

 

[willir]

Hint...
You can use the Query Builder to debug your SQL statement.

Copy the SQL statement to your clipboard.
Start the QueryBuilder with the wizard, and do not add any tables when prompted. Switch to SQL view (From the menu, "View" -> "SQL View"). Paste your SQL statement into the SQL view window. Then make sure to add a ";" semicolon to the end of the select statement.

When you execute the SQL statement within the SQL view, if there is an error, the error message will appear, and the cursor will high light the first problem area it finds.