Please help - adding "." to Middle Initial???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need help how to update a table where in the Mid Name field there are
initials. I want to be able to update with a period after the initial only
if there is an initital and skip the empty fields. Thank you in advance for
any help.
 
TotallyConfused said:
Need help how to update a table where in the Mid Name field there are
initials. I want to be able to update with a period after the initial
only
if there is an initital and skip the empty fields. Thank you in advance
for
any help.
Click to expand...

Create a replace query with this as the replace line under the MidName
field:

Iff(Not IsNull([MidName]), [MidName]&".", Null)

Tom Lake
 
Assuming the character û does not appear in any name,


Replace(Replace(Replace(fieldName, " ", "û"), " ", ". "), "û", " ")


should do the job: The first space, if any, is replaced by û, the second
space, replaced by a dot and a space, and then, the first û, replaced back
by a space.

That also assumes there is just one space between the first name and the
middle initial. You can always Replace(string," ", " " ) to remove
duplicated spaces:

Replace(Replace(Replace(Replace(Trim(fieldName), " ", " "), " ", "û"), " ",
". "), "û", " ")


Sure, there is a problem left: if there was already a dot, after the middle
initial, you now have two of them. But ... you should have guess the
solution to this problem, right?


Replace(Replace(Replace(Replace(Replace(Trim(fieldName), " ", " "), " ",
"û"), " ", ". "), "û", " "), "..", ".")





Hoping it may help,
Vanderghast, Access MVP
 
Ah, I was incorrect, I was thinking you were having just one field for the
full name. If you have a specific field for the middle initial, then,
indeed update to



Replace(fieldName + ".", ".." , "." )


with a condition about WHERE fieldName NOT IS NULL.


Vanderghast, Access MVP
 
I would think that the following would work to update all fields that
contained any text other that didn't end in a period.

UPDATE yourTable
Set [MidName] = [MidName] & "."
WHERE [MidName] Not Like "*."

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top