Removing Records Criteria

[newbie2181]

Hi,
I have aquery set up that does everything that i want it to except one little bit. The data that i am running the query on can contain prices or the word refer. Is there any way that this can be set up so that i run the query and all the Refers are replaced with 0?

Thanks a lot

dan

 

[nicsin]

try this:


select iif([columnName] = 'refer',0,[columnName]) as Price
from tableName


I guess you can tailor that to suit your structure

 

[dhookom]

Good answer from nicsin. I would only add that you might want to explicitly return a numeric value from the text field.
select iif([columnName] = 'refer',0,Val([columnName])) as Price
from tableName

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[newbie2181]

ok thanks for that i can understand whats going on with most of that but what does the "as price from table name" do?

 

[dhookom]

"As Price" creates a column alias. "From tablename" is just part of the SQL in almost every query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[newbie2181]

ow right ok cheers for that mate

Dan