Combine CASE and UPDATE statments in VBA

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top