Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    36

    Unanswered: Updating a Field using Various Text Functions

    I have the following code in my Before_Update event:

    If IsNull(Me.LSID) Then

    Me.LSID = IIf((Len(Left([LastName], 4)) >= 4), _
    UCase(Left([LastName], 4)), UCase((Left([LastName], 4))) _
    & (Space(4 - (Len([LastName]))))) & " " & _
    IIf((Len(Right([SSN], 4)) >= 4), Right([SSN], 4), _
    (String(4 - (Len([StudentID])), "0") & (Right([StudentID], 4))))

    End If

    The LSID is null and the code is stepped into, but it doesn't get updated the way I thought it woud. This is the same code I use in an update query to update the LSID and it works fine. Do I need to change it in some way when I move it to VB code? It doesn't give me any compile errors, it just doesn't update the field.

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the closest i can get to your iif logic is:

    Code:
    	If Len(Left([LastName], 4)) >= 4 then ' greater than is impossible
    		Me.LSID = UCase(Left([LastName], 4))
    	Else 
    		Me.LSID = UCase(Left([LastName], 4)) & Space(4 - Len([LastName]))
    	End If
    
     	Me.LSID = Me.LSID & " "
    
    	If Len(Right([SSN], 4)) >= 4 then ' greater than is impossible
    		Me.LSID = Me.LSID & Right([SSN], 4)
    	Else
    		Me.LSID = Me.LSID & (String(4 - Len([StudentID]), "0") & Right([StudentID], 4)
    	End If

    anyway, assuming you want this strange code to happen in immediate if, your statement should read:

    Code:
    iif(Len(Left([LastName], 4)) >= 4, UCase(Left([LastName], 4)), UCase(Left([LastName], 4)) & Space(4 - Len([LastName]))) & " " & iif(Len(Right([SSN], 4)) >= 4, Right([SSN], 4), String(4 - Len([StudentID]), "0") & Right([StudentID], 4))

    izy

  3. #3
    Join Date
    Apr 2003
    Posts
    36
    Thanks, I separated it and it works fine. But, what is the difference between your final code (the IIF) and my original code?

    Just curious.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know!

    you had more parenthesis than necessary and maybe there was an error somewhere amongst them. i tried counting through your open parenthesis and got lost too many times.

    i reformatted your iif as if-then-else, killed as many parenthesis as possible, and rebuilt the iif.

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •