Putting an IF Statement into a Query in Access

[lee.bridle07]

Hi, I wonder if someone can help?

I've got a simple query I've set up, which is pulling in data from a
couple of tables.

In one of the tables, I've got a collum that has GM or KG.

What I want is a collumn in the query, that if the collumn (UOM) that
has GM or KG contains GM, then divide another collumn (Quantity) by
1000.

So, the way I'd see it is:

=IF (UOM=GM (Quantity / 1000))

Just can't get my head around how I would put this into the query.

Thanks for your time!

Lee

 

[RonaldoOneNil]

To add a column in a query, just give it a name followed by a colon and then
enter your statement like this (it is an IIF statement with 3 parts -
condition, if true, if false)

Weight: IIF([UOM]="GM",[Quantity]/1000,[Quantity])

 

[BruceM]

At the top of a blank column in query design view:

Calc_UOM: IIf([UOM] = "GM",[Quantity/1000],"")

Use whatever name you like in place of Calc_UOM. Replace the double quotes
with what if anything you would have appear if [UOM] does not equal "GM".

Help has more information about IIf.

As a suggestion, before adding the column, click View >> SQL and look at the
SQL code. Switch to design view, make the suggested change, switch to
datasheet view to be sure you are getting the intended result, then look at
the SQL view again to see how the change shows up there.

 

[BruceM]

Oops. Posted essentially a duplication of an earlier answer, which for some
reason did not show up in my newsreader at first.

At the top of a blank column in query design view:

Calc_UOM: IIf([UOM] = "GM",[Quantity/1000],"")

Use whatever name you like in place of Calc_UOM. Replace the double
quotes with what if anything you would have appear if [UOM] does not equal
"GM".

Help has more information about IIf.

As a suggestion, before adding the column, click View >> SQL and look at
the SQL code. Switch to design view, make the suggested change, switch to
datasheet view to be sure you are getting the intended result, then look
at the SQL view again to see how the change shows up there.

Hi, I wonder if someone can help?

I've got a simple query I've set up, which is pulling in data from a
couple of tables.

In one of the tables, I've got a collum that has GM or KG.

What I want is a collumn in the query, that if the collumn (UOM) that
has GM or KG contains GM, then divide another collumn (Quantity) by
1000.

So, the way I'd see it is:

=IF (UOM=GM (Quantity / 1000))

Just can't get my head around how I would put this into the query.

Thanks for your time!

Lee