Can't update using docmd.runsql

[davbib]

Hi

I'm trying to modify a field in a table by calling a function. It runs without error, however the value I'm trying to modify is replaced with a null value rather than the value the function provided. Here is my code:

Public Sub Clean()

Dim Sql
Sql = "Update [tablename] SET [Item] = CleanFunction([Item]) WHERE [shipped by] like 'ACME'"

DoCmd.RunSQL Sql

End Sub

Public Function CleanFunction(Item)

If Item Like "??JD*" Then
Item = Mid(Item, 6, 99)
Else: End If

End Function

Could someone help me out? I'm not sure what I'm doing wrong here...I've tried combinations of quotes, etc with no success.

 

[vbajock]

You are returning a null value on one of your code paths. Try this:

Public Function CleanFunction(byval sItem as String) As String

If sItem Like "??JD*" Then
sItem = Mid(sItem, 6, 99)
Else
CleanFunction=sItem
End If

End Function

 

[TheAceMan1]

How are ya davbib . . .

One routine will do for this:

[blue]Public Sub Clean()
   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
   
   SQL = "Update [TableName] " & _
         "SET [Item] = IIf([Item] Like '??JD*',Mid([Item], 6, 99),[Item]) " & _
         "WHERE [shipped by] like 'ACME';"
   db.Execute SQL, dbFailOnError
   
   Set db = Nothing

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[davbib]

Greetings VBAJock and Acemam!

VBAJock's code runs, but the result is the same. I end up with a null value in the Item field. The function accurately converts ZZJD1234 to 1234, but my table now shows no item.

Aceman's code is what I started with. I wanted to change to a function since I am having to update the item in multiple tables and I was re-writing the same section of code in my Sub. The clean up I need to do has multiple conditions I need to address, but I just wanted to keep my example simple.

 

[davbib]

I found it...VBAJock's code works, if you put the Cleanfunction = Sitem AFTER the EndiF. I just needed another cup of coffee to see that.

Thanks again!

 

[vbajock]

If you put it after the Endif, then it is going to return that value no mater what your string parsing does. You might want to look at your pattern ??JD*

 

[TheAceMan1]

davbib . . .

[blue]vbajock's[/blue] code doesn't return [blue]Mid(sItem, 6, 99)[/blue] ... code should be:

[blue]Public Function CleanFunction(ByVal sItem As String) As String

   If sItem Like "??JD*" Then
      [purple][b]CleanFunction[/b][/purple] = Mid(sItem, 6, 99)
   Else
      CleanFunction = sItem
   End If

End Function[/blue]

BTW ... [blue]Mid("ZZJD1234", 6, 99)[/blue] returns [blue]234[/blue]! You'd have to change the starting position to 5 to get [blue]1234[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[vbajock]

Yes, you are right, I wish I had time to actually run this stuff to debug it.....