none
MS Access SQL Query - Manipulate RRS feed

All replies

  • Is there a way to add to the query to detect those situations and just add an ")" to the result where found? For the second instance, just remove the open "(" since not comes after.

    Hi cdtakacs1,

    You could make a user-function in VBA that does all the work, e.g.

    SELECT 'ZRND' AS [Order Type], ChangeExistingPFP.[PFP Code] AS IO, Tune_Alias(ChangeExistingPFP.N_Alias) AS Description
    FROM ChangeExistingPFP
    WHERE ChangeExistingPFP.N_Alias IS NOT NULL;

    with

    Function Tune_Alias(ByVal tmp_alias As String) As String
      
      tmp_alias = Left(tmp_alias, 40)
      If (Right(tmp_alias, 1) = ")") Then
        tmp_alias = Left(tmp_alias, Len(tmp_alias) - 1)
      End If
      
      'all other conditions
      Tune_Alias = tmp_alias
    End Function
    
    

    Imb.


    • Edited by Imb-hb Sunday, August 26, 2018 2:15 PM
    Sunday, August 26, 2018 2:14 PM
  • I would also use a VBA function.

    Just past the following into a public module. It should handle both cases. You can remove the second parameter and hard code the length if you like, but I usually prefer to make something like this flexible for future use.

    Public Function LeftParenFix(ByVal value As Variant, ByVal lngMaxLen As Long) As Variant
       Dim retVal As Variant
       Dim lngLen As Long
       Dim lngParen1 As Long
       Dim lngParen2 As Long
      
       lngLen = Len(Nz(value, ""))
       If lngLen > lngMaxLen Then
          retVal = Left(value, lngMaxLen)
          lngParen1 = InStrRev(retVal, "(")
          lngParen2 = InStrRev(retVal, ")")
          If lngParen1 = lngMaxLen Then
             retVal = Left(value, lngMaxLen - 1)
          ElseIf lngParen1 = lngMaxLen - 1 Then
             retVal = Left(value, lngMaxLen - 2)
          ElseIf lngParen1 > lngParen2 Then
             retVal = Left(value, lngMaxLen - 1) & ")"
          End If
       Else
          retVal = value
       End If
      
       LeftParenFix = retVal
    End Function

    Sunday, August 26, 2018 2:33 PM
  • Your query is a select query and therefore, the data is not changed.

    If you are using Access desktop, you simply use the function in your query

    SELECT 'ZRND' AS [Order Type], ChangeExistingPFP.[PFP Code] AS IO, LeftParenFix(ChangeExistingPFP.N_Alias,40) AS Description
    FROM
    ChangeExistingPFP
    WHERE
    ChangeExistingPFP.N_Alias IS NOT NULL;

    Sunday, August 26, 2018 5:05 PM
  • As long as you are using Access Desktop with linked tables, the source of the query doesn't matter.

    Why not simply try it? You have nothing to lose.

    Sunday, August 26, 2018 8:28 PM
  • I'm currently using the SQL query I originally posted. However, the suggestions said to use a VBA.  Can I still use a VBA even though the tables are Linked? If so, how do I incorporate? I merely place the provided VBA in a module and it will automatically execute when I run my query?

    Hi cdtakacs1,

    I would say: yes. Did you try it?

    Imb.

    Sunday, August 26, 2018 8:34 PM
  • Happy to help. If your question is answered, you should mark the answer(s) as such.
    Sunday, August 26, 2018 11:08 PM