Solved Find & Replace

[SamG]

Hi there,
I've used Access before but it's been a while so I consider myself a newbie again. I am currently using Access 365 and Windows 10.
I am working on some sales analytics and need to cap sales at $500k. I don't want to exclude any sales, but rather I want to do a find and replace and make all those items with sales greater than $500k equal to $500k. I hope that makes sense?
There is already a query in place (created by my predecessor), that brings this field into a new table so I'm hoping I can find and replace within this query.
The query starts with SELECT Sum([A4-planned placement ty sales].[26WK PROJ SALES]) AS [PROJ 26WK SALES]
I would need to do the find and replace after the sales have been summed.

Thanks so much!
Sam

 

[plog]

I don't think your method is the way to go. The way to go is to build a query on top of your existing query and use a conditional statement to turn anything over 500k into 500k. This new query would do that like so:

SELECT Iif([YourSalesField]>500000, 500000, [YourSalesField]) AS SalesLimited
FROM YourExistingQuery

Here's more info on the Iif I used:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

 

[SamG]

Thank you!
This is what I used:
Iif([CAPPED SALES]>500000, 500000, [CAPPED SALES]) as PROJ 26WK SALES,
but when I go to save the changes I get the following message
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
And then it highlights 26WK as seen in the above statement.
Thanks for your patience!

 

[MajP]

You can not have a space in a name without []. Proj26WkSales or [Proj 26WK Sales]. I would not use spaces, use undersores Proj_26Wks_Sales

 

[plog]

You are not doing yourself any favors with your names.

For any names (table, field, query) only use alphanumeric characters and underscore. No spaces, no special characters.

 

[SamG]

You can not have a space in a name without []. Proj26WkSales or [Proj 26WK Sales]. I would not use spaces, use undersores Proj_26Wks_Sales

Thank you for the info! Again, total newbie so I didn't know that.

 

[SamG]

You are not doing yourself any favors with your names.

For any names (table, field, query) only use alphanumeric characters and underscore. No spaces, no special characters.

Thank you!

 

[SamG]

You are not doing yourself any favors with your names.

For any names (table, field, query) only use alphanumeric characters and underscore. No spaces, no special characters.

Haha! Thanks, but I didn't create this database, my predecessor did, but I'll keep that in mind for future use.

 

[SamG]

It worked! Thank you all so much for your help!