Convert Excel Formula to Access Code

[foxhound]

Hello,

Can anyone tell me how to accomplish this in Access code where C4 = rst!mailingname?

=RIGHT(C4,(LEN(C4)-FIND(",",C4,1)))

Thanks for any assistance.

Foxhound

 

[SydneyGeek]

Let's say the new string is strResult.

With rst
  .MoveFirst 'Go to first record
  Do Until .EOF
  strResult=RIGHT(![mailingname],(LEN(![mailingname])-FIND(",",![mailingname],1))) 
  'Do something with strResult here...
  .MoveNext
  Loop
End With

Note: You may need to use INSTR instead of FIND.

If you want to write the result to another field in the same recordset, you could do this:

With rst
  .MoveFirst 'Go to first record
  Do Until .EOF
  .Edit
  ![MyField]=RIGHT(![mailingname],(LEN(![mailingname])-FIND(",",![mailingname],1))) 
  .Update
  .MoveNext
  Loop
End With

Alternatively, you could create a query with an expression that does the work for you. Then just use the result of the query for downstream processing. Advantage: everything updates as you need it, no need to store the calculated data.

Denis

 

[foxhound]

Denis, thanks for your help. I tried this changing find to instr but I still cannot get to work. My database does not like the "right" piece of the code. Is there a specific library that needs to be referenced in order for this function to work?


Foxhound

 

[Mile-O-Phile]

If you'd taken the InStr() function advice you were given and looked it up in the Help files you'd have seen that all you had to do was change the order of the arguments.


=RIGHT(rs.Fields("MailingName"),(LEN(rs.Fields("MailingName"))-InStr(1, rs.Fields("MailingName"), ","))