I’ll get back to working on the custom tags class tomorrow. Today I want to share a solution I worked out in order to swap numeric values in an Access DAO SQL statement.

So, I had a need to import records into a table where there may be existing records of the same type.

If there are existing records, they will already be in order by descending quantity.

Let’s say we have a table like this:

Fruit – OrderInt

Apples – 1

Bananas – 2

Kiwi – 3

This is an extremely simplified example, but let’s say I want to add Apricots and Dragonfruit and I want the numbers to be in numeric order changed for the list that is now in alphabetical order.

So I use a SELECT statement to pull up a recordset with the new fields imported with incremental numbers that were added by the import process for the order:

Fruit – OrderInt

Apples – 1

Apricots – 4

Bananas – 2

Dragonfruit – 5

Kiwi – 3

I want to change the order so I just walk the list and build a list of what numbers should be changed to what new number:

Apples – 1 should remain the same

Apricots – 4 should change to 2

Bananas – 2 should change to 3

Dragonfruit – 5 should change to 4

Kiwi – 3 should change to 5

Using the Access Switch function I can create an update statement as follows:

UPDATE fruittable SET OrderInt = Switch(OrderInt=1,1,OrderInt=4,2,OrderInt=2,3,OrderInt=5,4,OrderInt=3,5)

And that’s all I need to build. Notice that the Switch statement contains all the conditions of OrderInt from top to bottom of the wrongly ordered imported list with the corrected OrderInt for the updated list right after it.

I know the example may seem stupid here, but it’s just for simplicity’s sake. There is a reason why the application is doing it all these crazy gyrations. My job was simply to find the simplest way to reorder. Initially I had tried to write multiple SQL statements changing the OrderInt one at a time, but it quickly became very complicated with overlapping changes.

In this case I am using the Switch function. That function is available in Access but not SQL Server. This could be adapted to use directly in SQL server by modifying the SQL statement to use the CASE WHEN ELSE END keywords instead of the switch statement, or you could even write a custom User Function in SQL server called Switch to act just like the switch function in Access.