IIf Query

  • Thread starter Thread starter JoP
  • Start date Start date
J

JoP

I have a query set up to show all orders received that
have not been acknowledged. [PROM DATE] is Null.

I have inserted a column called 'Urgent' and I want it to
look at the part numbers from the above query and put the
text '24HR' into the column if it matches 1 of 8 different
part numbers.

I have used:-

IIf([RPN]="705046-000","24HR","") in the 'Urgent' column
criteria.

I do not want the query to use this as a selection, I
merely want it to fill in the field with '24HR' if it
matches the criteria.

I have no experience of VB so am wondering if anyone has a
solution I could use to put direct into the Query.

Thanks

Jo P
 
Hi,



UPDATE myTable
SET Urgent = iif(rpn='705046-00', '24HR', Urgent )



would update the Urgent column for all the records with 24HR if the rpn
value of the record is equal to 705046-00, else, it will leave the Urgent
field as it was (updating it by itself).

You can add a WHERE clause if you wish to limit the records to be
considered, such as



WHERE [From Date] IS NULL



Hoping it may help,
Vanderghast, Access MVP
 
Hi there

Thanks for this, but I can't make it work. It think I'm
putting the data in the wrong place.

Where do I put the UPDATE and IIF statement in the choices
below?

Field: Urgent
Table: SalesOrderItems
Sort:
Show:
Criteria:
Or:

Thanks

Jo
-----Original Message-----
Hi,



UPDATE myTable
SET Urgent = iif(rpn='705046-00', '24HR', Urgent )



would update the Urgent column for all the records with 24HR if the rpn
value of the record is equal to 705046-00, else, it will leave the Urgent
field as it was (updating it by itself).

You can add a WHERE clause if you wish to limit the records to be
considered, such as



WHERE [From Date] IS NULL



Hoping it may help,
Vanderghast, Access MVP


I have a query set up to show all orders received that
have not been acknowledged. [PROM DATE] is Null.

I have inserted a column called 'Urgent' and I want it to
look at the part numbers from the above query and put the
text '24HR' into the column if it matches 1 of 8 different
part numbers.

I have used:-

IIf([RPN]="705046-000","24HR","") in the 'Urgent' column
criteria.

I do not want the query to use this as a selection, I
merely want it to fill in the field with '24HR' if it
matches the criteria.

I have no experience of VB so am wondering if anyone has a
solution I could use to put direct into the Query.

Thanks

Jo P
Click to expand...


.
Click to expand...
 
HI,


Either:

Cut and paste the code in the SQL view (change MyTable to the real
table name, SalesOrderItems),


Either

change the query from a SELECT to a UPDATE query, a new line would
appear, UpdateTo. In that line, under Urgent (as field), type the iif
expression:


iif( [rpn] = "705046-00" , "24HR", [Urgent] )





Hoping it may help,
Vanderghast, Access MVP



JoP said:
Hi there

Thanks for this, but I can't make it work. It think I'm
putting the data in the wrong place.

Where do I put the UPDATE and IIF statement in the choices
below?

Field: Urgent
Table: SalesOrderItems
Sort:
Show:
Criteria:
Or:

Thanks

Jo
-----Original Message-----
Hi,



UPDATE myTable
SET Urgent = iif(rpn='705046-00', '24HR', Urgent )



would update the Urgent column for all the records with 24HR if the rpn
value of the record is equal to 705046-00, else, it will leave the Urgent
field as it was (updating it by itself).

You can add a WHERE clause if you wish to limit the records to be
considered, such as



WHERE [From Date] IS NULL



Hoping it may help,
Vanderghast, Access MVP


I have a query set up to show all orders received that
have not been acknowledged. [PROM DATE] is Null.

I have inserted a column called 'Urgent' and I want it to
look at the part numbers from the above query and put the
text '24HR' into the column if it matches 1 of 8 different
part numbers.

I have used:-

IIf([RPN]="705046-000","24HR","") in the 'Urgent' column
criteria.

I do not want the query to use this as a selection, I
merely want it to fill in the field with '24HR' if it
matches the criteria.

I have no experience of VB so am wondering if anyone has a
solution I could use to put direct into the Query.

Thanks

Jo P
Click to expand...


.
Click to expand...
Click to expand...
 
Hi there

Thanks for this, but I can't make it work. It think I'm
putting the data in the wrong place.

Where do I put the UPDATE and IIF statement in the choices
below?

Field: Urgent
Table: SalesOrderItems
Sort:
Show:
Criteria:
Or:

Thanks

Jo
-----Original Message-----
Hi,



UPDATE myTable
SET Urgent = iif(rpn='705046-00', '24HR', Urgent )



would update the Urgent column for all the records with 24HR if the rpn
value of the record is equal to 705046-00, else, it will leave the Urgent
field as it was (updating it by itself).

You can add a WHERE clause if you wish to limit the records to be
considered, such as



WHERE [From Date] IS NULL



Hoping it may help,
Vanderghast, Access MVP


I have a query set up to show all orders received that
have not been acknowledged. [PROM DATE] is Null.

I have inserted a column called 'Urgent' and I want it to
look at the part numbers from the above query and put the
text '24HR' into the column if it matches 1 of 8 different
part numbers.

I have used:-

IIf([RPN]="705046-000","24HR","") in the 'Urgent' column
criteria.

I do not want the query to use this as a selection, I
merely want it to fill in the field with '24HR' if it
matches the criteria.

I have no experience of VB so am wondering if anyone has a
solution I could use to put direct into the Query.

Thanks

Jo P
Click to expand...


.
Click to expand...
Click to expand...

You should first select the Update Query type from the Query Menu.

While in Query Design View, click on the Query Menu button.
Select Update query.
The design grid will then change to look like this:

Field
Table
Update To
Criteria
Or

On the Field line write
[Urgent]

On the Table line enter the name of the table.

On the Update To: line write
IIF(rpn='705046-00', '24HR', Urgent )

On the Criteria line write:
[From Date] IS NULL

When done, click on the query view tool button and select SQL view.

The finished SQL will be shown in the window.

Alternatively, you could just write the SQL in this window, as
indicated in Vanderghast's post,

UPDATE myTable SET Urgent = iif(rpn='705046-00', '24HR', Urgent)
Where [From Date] Is Null;

and the Grid view will fill in.
Of course you must always substitute your actual table and field names
for the generic [MyTable], [From Date], names usually given in these
news groups.
 
Hi,


After a second thought, in SQL text, it may be more standard to use:


UPDATE myTable SET Urgent = '24HR"
WHERE [From Date] Is Null AND rpn IN('705046-00' );



You can then easily increase the IN list of code:


UPDATE myTable SET Urgent = '24HR"
WHERE [From Date] Is Null AND rpn IN('705046-00' , 'xyx-00-1234' );



as example. Here, 24HR will update Urgent if the code is either 705046-00,
either xyx-00-1234


You can next switch back to the design view to see how you could have use
the designer, rather than doing the typing in SQL.


Hoping it may help,
Vanderghast, Access MVP


fredg said:
Hi there

Thanks for this, but I can't make it work. It think I'm
putting the data in the wrong place.

Where do I put the UPDATE and IIF statement in the choices
below?

Field: Urgent
Table: SalesOrderItems
Sort:
Show:
Criteria:
Or:

Thanks

Jo
-----Original Message-----
Hi,



UPDATE myTable
SET Urgent = iif(rpn='705046-00', '24HR', Urgent )



would update the Urgent column for all the records with 24HR if the rpn
value of the record is equal to 705046-00, else, it will leave the Urgent
field as it was (updating it by itself).

You can add a WHERE clause if you wish to limit the records to be
considered, such as



WHERE [From Date] IS NULL



Hoping it may help,
Vanderghast, Access MVP


I have a query set up to show all orders received that
have not been acknowledged. [PROM DATE] is Null.

I have inserted a column called 'Urgent' and I want it to
look at the part numbers from the above query and put the
text '24HR' into the column if it matches 1 of 8 different
part numbers.

I have used:-

IIf([RPN]="705046-000","24HR","") in the 'Urgent' column
criteria.

I do not want the query to use this as a selection, I
merely want it to fill in the field with '24HR' if it
matches the criteria.

I have no experience of VB so am wondering if anyone has a
solution I could use to put direct into the Query.

Thanks

Jo P


.
Click to expand...
Click to expand...

You should first select the Update Query type from the Query Menu.

While in Query Design View, click on the Query Menu button.
Select Update query.
The design grid will then change to look like this:

Field
Table
Update To
Criteria
Or

On the Field line write
[Urgent]

On the Table line enter the name of the table.

On the Update To: line write
IIF(rpn='705046-00', '24HR', Urgent )

On the Criteria line write:
[From Date] IS NULL

When done, click on the query view tool button and select SQL view.

The finished SQL will be shown in the window.

Alternatively, you could just write the SQL in this window, as
indicated in Vanderghast's post,

UPDATE myTable SET Urgent = iif(rpn='705046-00', '24HR', Urgent)
Where [From Date] Is Null;

and the Grid view will fill in.
Of course you must always substitute your actual table and field names
for the generic [MyTable], [From Date], names usually given in these
news groups.
Click to expand...
 
Back
Top