Access Formula

  • Thread starter Thread starter Santi
  • Start date Start date
S

Santi

What's the correct format that I need to include in the "Builder" in Access
that will return a value for the following argument.

If the value of the field name "brand" is = to "SUV" then I want my current
field to return a value "4x4"
 
Santi

?"in the Builder"? Where are you trying to do this? In a query?

I'm confused ... which is the name of the field and which is the value in
the field?

Are you saying that if your field named [brand] = "SUV", you want to see
"4x4"? Does that mean every SUV is a 4x4?

In a query, you could add a new field, something like:

NewField: IIF([brand]="SUV","4x4", ?????)

But what do you want to see if [brand] is NOT "SUV"? (that's what you need
to show where I left the ?????)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
What's the correct format that I need to include in the "Builder" in Access
that will return a value for the following argument.

If the value of the field name "brand" is = to "SUV" then I want my current
field to return a value "4x4"
Click to expand...

What's the context, Santi? In a Query, on a Form, in a table? What are the
other "Brand" values and what result should they imply?
 
I'm trying to do this in a query. Basically I want to do an IF formula that
depends on the value on another field. Not sure if this is posible in Access.
I just want to know the format of how I need to enter this in Access.
 
Jeff said:
Santi

?"in the Builder"? Where are you trying to do this? In a query?

I'm confused ... which is the name of the field and which is the value in
the field?

Are you saying that if your field named [brand] = "SUV", you want to see
"4x4"? Does that mean every SUV is a 4x4?

In a query, you could add a new field, something like:

NewField: IIF([brand]="SUV","4x4", ?????)

But what do you want to see if [brand] is NOT "SUV"? (that's what you need
to show where I left the ?????)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Santi said:
What's the correct format that I need to include in the "Builder" in
Access
that will return a value for the following argument.

If the value of the field name "brand" is = to "SUV" then I want my
current
field to return a value "4x4"
Click to expand...
Click to expand...

Jeff's quite right (as we've come to rely on!): using an expression
involving IIF will do what you ask. But that's not the way to do this:
it's a "patch", rather than a generaliseable, scalable solution. If you
subsequently found you had a lot of these transformations to do, you'd
end up with a hideously complex expression in your query, and even
moving the code to a VBA module (where you could build a suitable
function legibly) is ignoring one of the basic capabilities of a
relational database - the ability to relate fields across tables.

Have another look at your table design. If you can say "an SUV is a
4x4" then why not express that in table relationships? Ignoring the
observation that your "brand" sounds more like what I'd call a
"VehicleClass", you'd have an additonal table called "DriveType"
containing "4x4", "2wd" and whatever else might be possible. Then in
each main record you'd have a reference ("foreign key") to the
appropriate DriveType record.

If you only have the one replacement to do, then the IIF is ok: where
Jeff left ????? simply put [brand] and the original value will be used.
But if you want to allow for more possible substitutions use a
table-based solution.

Phil, London
 
Back
Top