Code is cutting off the last letter in a file name that it is creating

[Johnnycat1]

I am using the following code to rename a report when it sends the object in an email. The issue is that if there are more than two words in the VendorName field it drops the last letter of the vendor name.

eg. The saved file name is "PO 15-1192-1r0 Prysmian Powe 20151120.pdf" in lieu of "PO 15-1192-1r0 Prysmian Power 20151120.pdf"

If InStr(Replace(Me.Vendor, " ", Chr(255), 1, 1), " ") < 2 Then StrVendor = Replace(Me.Vendor, ",", "") Else StrVendor = Left(Replace(Me.Vendor, ",", ""), InStr(Replace(Me.Vendor, " ", "", 1, 1), " ") - 1)

nanos gigantum humeris insidentes

A dwarf on the shoulders of giants...

 

[Darrylles]

What are you intending?

It seems like:

IF 1st character of a string (with it's spaces replaced with 'y') is a space THEN return string with all commas removed ELSE return part of the string (up to the first space of string with all commas removed).

The first IF can't ever be true because you're checking for a space in a string where you've removed them all!
The ELSE statement is ALWAYS returned, which returns the left portion of a string (with all non-existent commas removed) up to the second space!

This just doesn't make any logical sense.

BTW, this should be in Access Modules (VBA Coding).

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.