Linking updated tables (1 Viewer)

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
i wanted to change the case in my linked tables. i exported each table in Excel and did work on each table to make the case from caps to proper.
I then imported each excel file back into my Access database. As each file imported, I was asked about the primary key. Knowing my linked tables relied on that primary key, i chose to use my own existing ID key.
Now, I cannot add any records. It is telling me my primary key is reading null. If I allow Access to determine my primary key, my tables will not link. Have I really blown it here?
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
Not blown it, just made a mess. Hopefully you have your original table somewhere (possibly a backup of the database that you made just in case something like this went sideways).

What you should do is UPDATE your table with the new data, not completely replace it wholesale. Suppose you have this data where [ID] is the primary key:

MyTable
ID, FirstName, LastName
1, JOHN, SMITH
2, STEVE, ALLEN
3, JUDY, MILLER

So you export it every field and clean it up to make it capitalized correctly. You then import it back into Access with a different name, let's say tmpTable. Just skip the primary key field when importing this data because its not necessary:

tmpTable
ID, FirstName, LastName
1, John, Smith
2, Steve, Allen
3, Judy, Miller

So to fix your names in MyTable you create an UPDATE query using MyTable and tmpTable. You link them via their [ID] fields and then update MyTable.FirstName to tmpTable.FirstName and MyTable.LastName to tmpTable.LastName. Run that and MyTable now has the correct data and you didn't screw up [ID].
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
Oh and to poor more salt in the wound--you don't even have to import/export this data to fix it. There's a simple function that will do that capitilzation fix for you:

https://www.techonthenet.com/access/functions/string/strconv.php

then your query becomes:

Code:
UPDATE MyTable SET FirstName = StrConv([FirstName], 3)

Of course the real lesson to all this is backup your data before you go changing it.
 

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
not to be picky, but it does not convert the "Mcclain" to McClain" ex. This will unprofessional in a mailing. I had to manually change them in Excel before importing them back to access Also PO Box is coming out as Po Box. If I corrected each of them, it would take a long time. there are over 7000 entries.

Thanks for the try. I feel one step closer to what I am looking for.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:25
Joined
Jan 23, 2006
Messages
15,364
StrConv ( text, conversion, LCID ) does each string.

When you have a McDonald or MacDonald, you will have to find such cases and adapt as you see fit. And then there's the Macpherson (no capital P???) Mackenzie....
Same issue with O'Donnell O'Hare etc, and with things like Oscar de la Renta.
see this link for names beginning Mc in the UK.
I suggest you look for patterns, and set up validation to do what you want when such a pattern is detected.

PO, RR, AFB and all state abbreviations for example will probably be affected.

Good luck. You aren't the first to see this as a shortcoming of strConv().
 
Last edited:

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
I am assuming by "set up a validation" it is something done in a query. the Conv did make them proper. How is this done?
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
Its done in a function. You build a function that takes your raw data and converts it to what you want, incorporating all the exceptions you've noted.

Code:
Function str_ValidateName(in_Name) As String

ret = str_Conv(in_Name,3)      
    ' return value, will hold value its passed to start with

If (ret starts with "Mc" ) then do this...
If (ret starts with "Mac") then do this ...
If (ret starts with "O'" then do this...
etc.

str_ValidateName = ret

End Function

When the function is done, your string is exactly like you want because you've coded for every exception.
 

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
where is this function added? For the "proper" conversion, I created a query
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
You add it to a module. Then inside a query you would call it like so:

GoodFirstName: str_ValidateName([FirstName])
 

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
I have worked the "front door" of this database for 15+ years but never the "back door" with modules, expressions, etc. I had this idea because I would usually convert my mailing list query to Excel then make these letter case adjustments. Then, I had the notion that I could send the "correct " version right to the printer for a mail merge.
All that to say, I figured from this forum how the convert case. But the modules, expressions, etc, I am not a clue--BUT-I want to learn if anyone will lead me...
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
I'll be happy to guide, but not lead.

If there's a general topic you want to learn about, the internet is full of tutorials--dive in. For specific advice and help on issues specific to your database, give it a shot first yourself then post back here with code and issues.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:25
Joined
Jan 23, 2006
Messages
15,364
imaquad,

People on the forum will assist/advise especially if you give an honest effort.
I suggest you set up some patterns -along the line that plog has identified.

I suggest some point form statement of the kinds of names you have found to be issues.
Then a statement of what conversion should occur.

One step at a time. And let us know your status or issue.
Good luck.
 

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
I have tried to figure out what it means in the formula above "then do this..." I opened a new module and placed the formula in the module but I have researched these few words and keep coming up blank.
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
I posted psuedo code. That's not real live working code, its a combination between English and actual code, sort of like an outline of a research paper. It just gives you basic structure, logic and descriptions of what its going to take to write actual code.

The 'then do this...' is up to you. What do you want to happen when it encounters a string that starts with 'Mc'? Thats what you replace the 'do this...' with.
 

imaquad

New member
Local time
Today, 05:25
Joined
Aug 4, 2017
Messages
7
Thank you for your speed at replies.

if I come across a name like McDonald and the conversion yields Mcdonald, I want the next letter after the MC to be upper case. This would apply to Mcdaniel, mcspadden, Mcgibbons, etc. Each name needs the next letter after "Mc to be upper case
 

plog

Banishment Pending
Local time
Today, 05:25
Joined
May 11, 2011
Messages
11,613
I completely understand your logic. I was telling you how to interpret my pseudo-code so you could turn it into actual code.

Here's a list of built-in functions you can use within your custom function to generate the results you want:

https://www.techonthenet.com/access/functions/

I know you are going to need Mid(). Possibly Replace(), UCase() and InStr as well.
 

Users who are viewing this thread

Top Bottom