IF Statement in query (1 Viewer)

[araza123]

hi guys,

What i am looking to do is similar to a IF statement in excel. I have a row of data with numbers. I need to add a row that will tell me what these numbers are based on the numbers. for example if a number is between 1-1000 its a new , 1001-2000 its used. Is this something easily done in access?

 

[pbaldy]

Sure:

IIf(FieldName < 1001, "New","Used")

But I suspect the real need is more complex, which will likely change the solution.

 

[June7]

Data shows as text field which means 1001 will sort before 2 and expression will return wrong results. Why is this a text field?

IIf(Val(FieldName) < 1001, "New", "Used")

 

[The_Doc_Man]

First, technically you don't add a ROW for this, you add a column... but second it isn't a column, it is a field. That may sound like I'm nit-picking, but it makes a difference to Access. Because third, if that number is how you identify something, you can make a QUERY to perform this identification on-the-fly and never actually bother to store anything. The query can be used to create a recordsource as though it were a table.

However, there is more to it than that. The REAL question is what ELSE do you intend to do with that information, because you might have a little bit of table twiddling to do to make this behave itself.

 

[arnelgp]

Data shows as text field which means 1001 will sort before 2 and expression will return wrong results. Why is this a text field?

IIf(Val(FieldName) < 1001, "New", "Used")

if Sale Odometer is Null, you will get #Error, so change to:

Iif(Val([Sale Odometer] & "") < 1001, "New", "Used")

 

[araza123]

First, technically you don't add a ROW for this, you add a column... but second it isn't a column, it is a field. That may sound like I'm nit-picking, but it makes a difference to Access. Because third, if that number is how you identify something, you can make a QUERY to perform this identification on-the-fly and never actually bother to store anything. The query can be used to create a recordsource as though it were a table.

However, there is more to it than that. The REAL question is what ELSE do you intend to do with that information, because you might have a little bit of table twiddling to do to make this behave itself.

Thank you for this, you are right and not nitpicking, i need to learn the access lingo so please correct me along the way. The end goal is to have this information described be used in a trend analysis. This is just one of the pieces we will be using. I suppose the best option is to correct a separate table with this information?

 

[araza123]

if Sale Odometer is Null, you will get #Error, so change to:

Iif(Val([Sale Odometer] & "") < 1001, "New", "Used")

Thanks for this info, what if I need to add multiple expression to the statement. something like if between 0-1000 new, if between 1001-2000 mid, if 2000-3000 used, if >3000 , unstable.

Something on excel would look like

=IF(K2>3001,"unstable",IF(K2>3000,"used",IF(K2>2000,"mid",IF(K2>1001"new",0))))

 

[arnelgp]

you can use Switch:

=Switch(K2 < 1001, "new", K2 < 2001, "mid", K2 < 3001, "used", True, "unstable")

 

[araza123]

you can use Switch:

=Switch(K2 < 1001, "new", K2 < 2001, "mid", K2 < 3001, "used", True, "unstable")

Thanks but is switch a function in access or excel?

this worked great Iif(Val([Sale Odometer] & "") < 1001, "New", "Used")

but if i need to append this to have it tiers i guess, so 0-1000 new, 1001-2000 mid, 2000> used. Something like this in access

 

[arnelgp]

its

Thanks but is switch a function in access or excel?

access and excel (vba)

Switch(Val([Sale Odometer] & "") < 1001, "new", Val([Sale Odometer] & "") < 2001, "mid", Val([Sale Odometer] & "") < 3001, "used", True, "unstable")

 

[araza123]

its

access and excel (vba)

Switch(Val([Sale Odometer] & "") < 1001, "new", Val([Sale Odometer] & "") < 2001, "mid", Val([Sale Odometer] & "") < 3001, "used", True, "unstable")

Thank you so much! I'll try it out!

 

[Galaxiom]

BTW Access also has the Partition() function which can be useful for dividing up values into groups. It is good when there are a large number of evenly spaced groups. The expression is much more compact than Switch() but not as configurable.

Although the partition size is configurable, the return values aren't. I have worked around this before by joining to a table to show whatever is desired instead.

I have never compared the performance of Partition against Switch.

 

[arnelgp]

BTW Access also has the Partition() function which can be useful for dividing up values into groups.

i can't image how Partition will be used in this case.
will you need additional column (again) to output "new","mid","used","unstable"?