Mid function

[access#1]

I am trying to rearrange text contained in a mid function. my code is:



Private Sub MaterialA_Reportable_Quantity_AfterUpdate()
Dim USDOTlen As Integer

If Me.MaterialA_Reportable_Quantity = False Then
USDOTlen = Len(Me.MaterialA_US_DOT_Description)
Me.MaterialA_US_DOT_Description = Mid(Me.MaterialA_US_DOT_Description, 7, USDOTlen - 6)
Else
Me.MaterialA_US_DOT_Description = Chr(34) & "RQ" & Chr(34) & ", " & Me.MaterialA_US_DOT_Description
End If

End Sub

The print result is: "RQ", Mercury Chloride, 6.1 un1624 PGII

I want to read: "RQ", 6.1 un1624 PGII, Mercury Chloride

How do I rearrange the mid function to do this?

Thanks,

[Dal Jeanis]

Try this -

Private Sub MaterialA_Reportable_Quantity_AfterUpdate()
   Dim USDOTlen As Integer
   Dim intComma As integer 
   Dim strUSDot1 As String
   Dim strUSDot2 As String
   If Me.MaterialA_Reportable_Quantity = False Then
      ' Drop the first six characters
      Me.MaterialA_US_DOT_Description = Mid(Me.MaterialA_US_DOT_Description & "", 7)
   Else
      ' Add "RQ" and if there is a comma in the USDOT Description
      ' then reverse the order of the two parts of the USDOT Description
      USDOTlen = len(Me.MaterialA_US_DOT_Description & "")
      intComma = Instr( , Me.MaterialA_US_DOT_Description & "", ",")
      If intComma = 0 Then 
          Me.MaterialA_US_DOT_Description = Chr(34) & "RQ" & Chr(34) & ", " & Me.MaterialA_US_DOT_Description
      Else
          strUSDot2 = left(Me.MaterialA_US_DOT_Description, intcomma - 1)
          strUSDot1 = IIF(USDOTlen>intcomma,trim(right(Me.MaterialA_US_DOT_Description, USDOTlen - intcomma)) & ", ","")
          Me.MaterialA_US_DOT_Description = Chr(34) & "RQ" & Chr(34) & ", " & strUSDot1 & strUSDot2
       End If
   End If
End Sub

I've explicitly handled the possibilities of a Null field, no comma in the field, and a comma at the very end of the field. I removed the last parameter from your Mid calculation; since "to the end of the input field" is the default, you don't have to calculate the remaining length.

[John_G]

Hi -

Do you want to do this only if MaterialA_Reportable_Quantity > 0? (I don't see why you are using "False" in your If statement, except for the fact that "False" is zero.)

From what I can see, if MaterialA_Reportable_Quantity > 0, you want to reverse the two parts of the description, which are separated by a comma. Something like this should work:

Dim CommaPos as integer, strTemp as string

strTemp = Me.MaterialA_US_DOT_Description
CommaPos = instr(strTemp, ",")
Me.MaterialA_US_DOT_Description = Chr(34) & "RQ" & Chr(34) & ", " & trim(mid(strTemp,CommaPos+1)) & ", " & left(strTemp, CommaPos - 1)

Please clarify if I have misunderstood your logic.

HTH

John