IF Statement

[ximistar]

Hi guys, hope you are all well.

I am using the Query design wizard and I have selected my table "Toner Stock"

and the fields Toner and Quantity.

What I want this query to do is the following:

I want it to get all the toners showing <2 as quantity. Which I have done previously using the following as the Criteria: "<2" in the Quantity field.

Now what I want it to do is:

if the quantity is 0 then add(+) 2 to make quantity 2

if the quantity is 1 then add(+) 1 to make quantity 2

I am just after words that I need to put in the Criteria to do this.

Thanks a lot for your help, you guys are amazing!

 

[JHB]

Are you going to update a field, then create and update query?

Use the criteria [quantity]<2
to get all records in which [quantity]=0 or [quantity]=1 then update the [quantity] field to 2.

 

[ximistar]

Are you going to update a field, then create and update query?

Use the criteria [quantity]<2
to get all records in which [quantity]=0 or [quantity]=1 then update the [quantity] field to 2.

Hi JHB,

what I would like to do is get all the toners in my database that are below quantity 2. Once that is done I would also like the query to update the quantity field making it a total of 2.

so if toner HP 78A is quantity is 0, I want the query to +2 to that.
If toner HP 45A is quantity is 1, I want the query to +1 to that.

I am just unsure what to put in the Criteria field to do this. I would like to automatically do it to save time.

Thanks.

 

[JHB]

..
I am just unsure what to put in the Criteria field to do this. I would like to automatically do it to save time.

Criteria is:

[quantity]<2

 

[arnelgp]

this is a select query.

Select TonerName, TonerQty, 2 - (TonerQty) As [Needed Toner] From TonerTable Where TonerQty < 2

 

[ximistar]

Criteria is:

[quantity]<2

I have this code and it is working fine.

But I now want is those IF statements that I stated above. So it is easier for the user to know how much quantity of each toner to Order, let me try be more clear.

Table 1 (Toner Stock)

Toner Name Quantity
HP 55A 3
HP 45A 0
HP 25A 1

So now The query (Low Stock Toners) Runs using <2
Then it will display HP 45A and 25A showing both its quantity

So far I have done all of this. Now what I want is the following

I want another query lets say to get the data from the previous query but in quantity I want it to change it to total 2.
So for HP 45A its stock is 0 I want it to add 2 to it so it totals 2
For HP 25A I want to add 1 to it so it can total 2:

That way the user knows how much quantity of each he needs to order so the query should show the following:

Toner Name Quantity
HP 45A 2
HP 25A 1

I hope this makes sense, I just want to know what exactly to put into the SQL statement/ Criteria.

Thanks guys

 

[JHB]

I've made a small sample for you, 2 queries, one which select, one which update.

 

[ximistar]

I've made a small sample for you, 2 queries, one which select, one which update.

Hi! Thanks so much this is exactly what I was looking for, works a treat. Much appreciated.

 

[JHB]

You're welcome, good luck.