IF Statement in query

araza123

New member
Local time
Today, 07:50
Joined
Apr 6, 2022
Messages
24
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?

1649714747862.png
 
Sure:

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

But I suspect the real need is more complex, which will likely change the solution.
 
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")
 
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.
 
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")
 
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?
 
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))))
 
you can use Switch:

=Switch(K2 < 1001, "new", K2 < 2001, "mid", K2 < 3001, "used", True, "unstable")
 
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")
1649776149946.png

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
 
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")
 
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!
 
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.
 
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"?
 

Users who are viewing this thread

Back
Top Bottom