Seeking best practice advice - converting VB to VBA or SQL query? RRS feed

All replies

  • What about using a Case Statement? The difference being you won't use a Cell, but rather a field.

    Select Case [Country]
       Case "AUT"
       Case "BEL"
    End Select

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Saturday, September 8, 2018 10:15 AM
  • Inside the first Case statement you could use an If... ElseIf... approach.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Saturday, September 8, 2018 12:39 PM
  • Inside the first Case statement you could use an If... ElseIf... approach.

    Hi cdtakacs1,

    … Or nested Case statements.

    Case statements are not always suitable: if you need to test for conditions that are independant of each other, then you can use the If Then Else construction.


    Saturday, September 8, 2018 1:29 PM
  • Trying to test this out and having trouble.

    Hi cdtakacs1,

    Not realised you need this as SQL-string. In my mind I was thinking of VBA.


    Saturday, September 8, 2018 3:57 PM
  • What exactly are you trying to do?  Are you trying to build a query or create a VBA procedure?

    Your original code was clearly VBA, but your above example is SQL, so we are confused.  Please give us the bigger picture.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Saturday, September 8, 2018 5:14 PM
  • It depends on what you are doing, but SQL is probably your best bet.

    But why not have a Cost Center table which is related to the data, then it would be a standard join between 2 tables, no more IIFs()...

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Saturday, September 8, 2018 10:50 PM
  • The way I would approach this is to put all of those translation values into a table. You don’t want to adopt a design in which you hire developer to write code to change a word document, anymore then hard coding such data translates inside o your code.

    The beauty of above, is NOW you don’t write new code, or even have to modify your code. You are free to change, or add new country codes (And MORE important is you can build forms etc. for the USERS of the application to edit + maintain these sets of translation values.

    So in “general” you don’t want to hard code. Think of an accounting package – we would not write new code every time we need some new GL account for example.

    Looking at what you have, it looks like some kind of “country” code is to be translated into a given set of values.

    So I would build a table like this:































    Now in code, you can translate your given value based on above table.


    Process the data

    MyValue = dlookup("Cvalue","tblCountry",left(K1,3))

    MyValue = MyValue & O1

    In other words, you want to put all those actual data values into a table. You then update or translate based on that table.

    So hard coding a whole bunch of information into code like you have is NOT going to be a successful approach to managing data and information with a database system.

    You can’t build a information system with a bunch of values hard coded into the system.

    I would sit down, and outline what the translates look like, and build a table of the coutnries. Once you build a correct data model then VERY little code is required. This is the “essence” of a relatondal database – it saves MASSIVE amounts of code, and  you in general VERY rare have to write large tracks of code to do some kind of lookup for a value.

    It not clear what column “O1” is here, and it not clear why a length check is occurring (LEN(01), but if length is of some importance, then that data could be included into the above translate table.

    It also not clear if this is a one time record processing of data, or is some expression?

    Access is a relational database. Anytime you see a WHOLE bunch of code with hard coded values that belong in a table is quite much a suggest that the approach being used is wrong. You most definatly do not want to use a spreadsheet like approach with a database system – it will not work, but worse you do large amounts of code and work when its not necessary.

    So build a country translate table for the values. It not 100% clear by looking at what you have if the O1 value is to come from the translate table, or the current record. However, I suspect that for form display etc., you have a Y1 values etc. (and Y1 is a horrifying name for a colum by the way – what does Y1 mean?).

    You don’t want the concept of say “year 1” in your database. If you need the customers first year of data, then you pull it from data tables to get that information.

    I mean, in a accounting package, we don’t have Y1 or year 1. We have a bunch of rows, and if we need that customers first year of data or invoicing or whatever, we write a query to return the data.

    You want to think of a data model. Realize and think of how say an accounting system works – they work fine, and you an have 1, or 10,000 different GL accounts – no code changes occur for such setups.

    So the answer here and how we do this I Access?

    Well, with correct table designs, you likely would wind up with zero, or perhaps 2-3 lines of code, and be done.


    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, September 8, 2018 11:48 PM

    LEN(Mid([Cost Center - ID], InStr([Cost Center - ID], "_") + 1)

    Hi cdtakacs1,

    If I understand right, then the LongName starts with a "1", ends with the part after the last "_" of the CostCenter_ID, and is 14 characters long.

    You could make a function, with CostCenter_id as parameter:

    Function LongName (CostCentr_id as String) As String
      LongName = "1" & Right("0000000000000" & Mid(CostCenter_id,InstrRev(CostCenter_id,"_") + 1),13)
    End Function

    You can place this function in a general module.

    You can see, I dropped all spaces from the names, to make thing a lot simpler.


    Sunday, September 9, 2018 3:12 PM
  • Can you help me accommodate those two situations as well?

    Hi cdtakacs1,

    What is the systematics of these "CA01" and "JP"?

    Can you give some examples just like you did before with:

    Cost Center - ID   Cost Center Long Name
    - 8011_1124807   10000001124807
    - 7070_1013046741   10001013046741
    - 2022_224101   10000000224101
    - 0000_000000   10000000000000
    - 7072_1014756   10000001014756


    Sunday, September 9, 2018 6:22 PM
  • Cost Center - ID	Cost Center Long Name 
    JPN - 0000_17800008	JP100017800008
    USA - US04_8702	        CA010000008702

    Hi cdtakacs1,

    A little more dynamic:

    Function LongName(CostCentr_id As String) As String
      Dim prefix As String
      Select Case Left(CostCentr_id, 3)
      Case "JPN":     prefix = "JP1"
      Case "USA":     prefix = "CA01"
      Case Else:      prefix = "1"
      End Select
      LongName = prefix & Right("0000000000000" & Mid(CostCentr_id, InStrRev(CostCentr_id, "_") + 1), 14 - Len(prefix))
    End Function

    Perhaps you have still some other modifications.


    Monday, September 10, 2018 8:10 AM