1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Combine CASE and UPDATE statments in VBA

Discussion in 'Business Applications' started by dmstrakos, Nov 6, 2011.

Thread Status:
Not open for further replies.
  1. dmstrakos

    dmstrakos Thread Starter

    Joined:
    Jul 14, 2011
    Messages:
    1
    First, is it possible to build a UPDATE statement to update a table based on a CASE statement in VBA? If not, I guess you can stop reading now and I'll start working on other options.

    If so, I could use some help converting the following SQL statement into an UPDATE / CASE statement that I can use in VBA.

    SELECT IAS_Personnel.RAS_PAS, IAS_Personnel.FySelected, IAS_Personnel.PAFSC,
    IIf(Left([PAFSC],1)
    In ("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"),
    Right([PAFSC],Len([PAFSC])-1),[PAFSC]) AS Core_AFSC,
    IIf(Left(TRIM([Core_AFSC]),2)=11,"11xx (Pilot)",
    IIf(Left(TRIM([Core_AFSC]),2)=12,"12xx (Nav)",
    IIf(Left(TRIM([Core_AFSC]),3)='13B',"13Bx (Air Battle Manager)",
    IIf(Left(TRIM([Core_AFSC]),3)='13S',"13Sx (Space/Missle)",
    IIf(Left(TRIM([Core_AFSC]),3)='14N',"14Nx (Intel)",
    IIf(Left(TRIM([Core_AFSC]),3)='15W',"15Wx (Weather)",
    IIf(Left(TRIM([Core_AFSC]),3)='17D',"17Dx (Cyber)",
    IIf(Left(TRIM([Core_AFSC]),3)='21A',"21Ax (Maintenance)",
    IIf(Left(TRIM([Core_AFSC]),3)='21M',"21Mx (Munitions)",
    IIf(Left(TRIM([Core_AFSC]),3)='21R',"21Rx (Logistics Readiness)",
    IIf(Left(TRIM([Core_AFSC]),3)='31P',"31Px (Security Forces)",
    IIf(Left(TRIM([Core_AFSC]),3)='32E',"32Ex (Civil Engineer)",
    IIf(Left(TRIM([Core_AFSC]),3)='35P',"35Px (Public Affairs)",
    "Not a yet"))))))))))))) AS Career_Field
    FROM IAS_Personnel
    WHERE IAS_Personnel.RAS_PAS="RAS";

    My original plan was to simply run the query then build a report based of the result of. However, I ran into the limit for IIF statements and I still have several cases to add.

    The SQL query worked like a charm until I hit the limit on the IIF statement which is forcing me to write a CASE statement in VBA.

    Rather than build a query just for the report I need, I now think it would be better to simply add the required fields (Core_AFSC and Career_Field) to tbl_IAS_Personnel , then build a simpler query on which to ultimately build the report I need.

    All this due to a database with some fairly dirty data. :mad:

    Thanks for the help in advance.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    dmstrakos, welcome to the Forum.
    Yes you can use Case or If/Then/Else with VBA created SQL.
    You can also use a VBA Module with your original query to replace the IIFs.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025753

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice