Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    IIf Update Query working OK but a better way?

    Hi,

    I have set up this query in the builder (sql below & db attached):

    Code:
    UPDATE Table3 SET Table3.Item = IIf([Table3]![Item]="Coffee","70%",IIf([Table3]![Item]="Tea","60%",IIf([Table3]![Item]="Herbal Tea","50%",IIf([Table3]![Item]="Vodka","100%",IIf(IsNull([Table3]![Item]),"Get a drink",[Table3]![Item])))));
    This is working. Null fields are covered as well as those which are not the above are left unchanged.

    How could I do a
    Code:
    SELECT CASE
    in the query builder instead or would this have to be done from a module?

    Thanks

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    The other ways you can get the same effect is by using switch or creating a function:

    Switch

    The syntax for the Switch function is:
    Switch ( expression1, value1, expression2, value2, ... expression_n, value_n )

    Now to modify the code that you have posted:

    Switch([Item] Is Null,"Get a Drink",[Item]="Coffee","70%",[Item]="Tea","60%",[Item]="Herbal Tea","50%",[Item]="Vodka","100%")

    To learn more about Switch:

    http://www.techonthenet.com/access/f...ced/switch.php

    Function

    The second possible way is creatin a function and then using it in your query criteria. i have done the following:

    Option Compare Database
    Dim strFieldName As String
    Dim strResult As String
    Function MultipleChoice(FieldName) As String
    Select Case FieldName
    Case Is = "Coffee"
    strResult = "70%"
    Case Is = "Tea"
    strResult = "60%"
    Case Is = "Herbal Tea"
    strResult = "50%"
    Case Is = "Vodka"
    strResult = "100%"
    Case Else
    strResult = "Get a Drink"
    End Select
    MultipleChoice = strResult
    End Function


    Now after saving this is a module in your db.Criteria will be MultipleChoice([Item])

    if this solves your problem mark the thread solved.

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    OK, this is looking good - I was wondering what would be the syntax with SWITCH if one of the fields needs to either be left alone or marked with "error" if it is not any of the above e.g. Coffee, Tea, NULL etc. as in the last part of my IIf statement:

    Code:
     
    [Table3]![Item]

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    The syntax to leave a value as it is taking your example will be:
    Switch([Item] Is Null,"Get a Drink",[Item]="Coffee","70%",[Item]="Tea","60%",[Item]="Herbal Tea","50%",[Item]="Vodka","100%",[Item] Is Not Null Or [Item]<>"Tea" Or [Item]<>"Coffee" Or [Item]<>"Herbal Tea" Or [Item]<>"Vodka",[Item])

    So if the value of the Field is neither null or any of the other drinks then the value of the field is left alone.

    needless to say in the above condition the value of the field can be marked error by using the following code:

    Switch([Item] Is Null,"Get a Drink",[Item]="Coffee","70%",[Item]="Tea","60%",[Item]="Herbal Tea","50%",[Item]="Vodka","100%",[Item] Is Not Null Or [Item]<>"Tea" Or [Item]<>"Coffee" Or [Item]<>"Herbal Tea" Or [Item]<>"Vodka","#Error#")

    If this solves your problem please mark this thread solved.

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Please mark this thread solved for the convinence of others with the same problem. If you have any more queries feel free to ask......

  6. #6
    Join Date
    Mar 2010
    Posts
    7
    OK - thanks for this.

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    What more do I have to do for the solved tag on this thread. please mark the thread solved

  8. #8
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Mark this thread solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  2. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  3. Query on chart not working properly!
    By Sim_ in forum Queries
    Replies: 0
    Last Post: 10-28-2009, 09:38 AM
  4. Query is not working
    By pushpm in forum Programming
    Replies: 3
    Last Post: 04-14-2009, 07:16 PM
  5. Replies: 1
    Last Post: 11-29-2005, 08:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums