Input masks not working (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 02:15
Joined
Jan 23, 2006
Messages
15,364
Try these queries.

Create a new query, go to sql view and add this
1:For CustomerContacts
Code:
SELECT Customer_Contacts.Contact, StrConv(Contact,3) AS Customer_Contact_Pc
FROM Customer_Contacts;


2: Repeat for VendorContacts
Code:
SELECT Vendor_Contacts.Contact, StrConv(contact,3) AS Vendor_Contact_PC
FROM Vendor_Contacts;

For consideration: Based on the recent database(post #19), it seems you are not experienced with Access or vba. Or else you have provided a database that does not match the requirement you stated or we have responded to.
Your sample data is insufficient to test the functions/responses being offered.

When doing data verification/reformatting do it as close to source as possible. That is do it once, not in every form, report etc.

Also, use sql or vba, not input mask.

Good luck with your project.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:15
Joined
Sep 21, 2011
Messages
14,048
For consideration: Based on the recent database(post #19), it seems you are not experienced with Access or vba. Or else you have provided a database that does not match the requirement you stated or we have responded to.
Your sample data is insufficient to test the functions/responses being offered.

.

@jdraw,
I seem to recall that Access2010 is actually multiple people using the same member name.
They come and go within their organisation and each person starts Access from scratch, with no experience of it at all.?
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:15
Joined
Jan 23, 2006
Messages
15,364
Possible. But the database in #19 is suspect in my view. I don't think OP would understand a function. Hope I'm wrong.
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
Some more contradictory examples

MacDonald, Mackie
O'Nion, Onion
DeSouza, Denton
Dean, DeAth, De'Ath, D'Ath
Lester, LeStrange
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:15
Joined
Jan 23, 2006
Messages
15,364
I've worked with people whose name was Macdonald and that's exactly how they spelled/wrote it. There was some history with respect to Mc vs Mac that was a determining factor. May not be relevant, but plays havoc with exceptions.
 

moke123

AWF VIP
Local time
Today, 02:15
Joined
Jan 11, 2013
Messages
3,852
heres a quick solution to the "LeBlanc" issue.
It tests for a Capital letter in the string, excluding the first letter.
If found a custom message box fires with 3 choices, "LeBlanc", "Leblanc" or whatever was typed in originally. At present its limited to about 10 letters because thats all that will fit as labels on the custom MsgBox buttons. Will have to find a way around that.

 

Attachments

  • FixCase.accdb
    436 KB · Views: 106
  • Capture.JPG
    Capture.JPG
    15.3 KB · Views: 273
Last edited:

ypma

Registered User.
Local time
Today, 06:15
Joined
Apr 13, 2012
Messages
643
Moke123. Thank you for your solution . Alas Access2010 appears to be working with access 2003 . i have thoroughly enjoyed seeing the cross fertilisation of ideas from a number of member. For me as a user, the post was very enlightening and instructive .


Happy New Year to One and All
Regards Ypma
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
Just tested that. Whilst it detects capitals placed in the middle of a word as stated, it doesn't help the issue where a required capital is omitted e.g. Leblanc

That isn't a criticism. I just think that aspect is unsolvable.
As Jack pointed out both MacDonald and Macdonald are in use.
 

moke123

AWF VIP
Local time
Today, 02:15
Joined
Jan 11, 2013
Messages
3,852
Just tested that. Whilst it detects capitals placed in the middle of a word as stated, it doesn't help the issue where a required capital is omitted e.g. Leblanc

That isn't a criticism. I just think that aspect is unsolvable.
As Jack pointed out both MacDonald and Macdonald are in use.

I agree and knew that.

The problem I had with the code I was using (same as what Gasman posted) was that it goes in the after update event of the control.
Code:
Me.MyTextBox = mixed_case(Me.MyTextBoc)
If you correctly spelled "LeBlanc" it would change it to "Leblanc". You could not go back and Change it to the correct spelling as it would always revert back to "Leblanc" At least with this you can make the change and it will stick.

I'm going to try it out in an app I'm in the middle of writing and see how the workflow goes.

The extra bonus was trying out the custom msgbox buttons procedure. I hope that the limit to the number of characters you can fit on a message box button doesn't ruin it.
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
The extra bonus was trying out the custom msgbox buttons procedure. I hope that the limit to the number of characters you can fit on a message box button doesn't ruin it.

If you are going to create a custom message box, there is no need to be limited by button size. For example there is a custom message box available from the rarely used Application Parts feature


However if I need a customised message box, I prefer to make my own from scratch. Then you have full control over formatting, number of buttons etc This is from my Attention Seeking example database

You can even add sounds to that if you really want to go overboard
 

Attachments

  • MsgBox1.PNG
    MsgBox1.PNG
    9.4 KB · Views: 285
  • CustomMsgBox.PNG
    CustomMsgBox.PNG
    19 KB · Views: 245

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
Thank you ALL for your suggestions and we have spent most of the weekend trying to get the data in the fields to upper and lower cases, without any success.

If we could please receive in our attached database a working Upper and Lower Case field we would appreciate it.

Thank you,
Crystal

Hi Crystal
Although we've gone off at a tangent, you can use any of the code from posts #4, #6 or #26 to do most of the work.
Any of those will change e.g. Fred Smith or FRED SMITH to Fred Smith.
However as noted above, you will still need to check names manually for exceptions e.g. Edward DeSouza
 

moke123

AWF VIP
Local time
Today, 02:15
Joined
Jan 11, 2013
Messages
3,852
If you are going to create a custom message box, there is no need to be limited by button size. For example there is a custom message box available from the rarely used Application Parts feature




Some how I knew I should have asked you first:eek:
Just finished a custon form:eek:

Never heard of the rarely used Application Parts feature. Can you point me in the right direction? Save me alot of googling?

Also noticed that Option Explicit is missing from one of the modules in what I posted. (Ahem,ypma :rolleyes:)
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
Never heard of the rarely used Application Parts feature. Can you point me in the right direction? Save me alot of googling?

Hi
Not sure whether it existed before A2010 but from that version onwards its a dropdown at the extreme left of the Create ribbon.
Personally I never use it but some people must like it .... ;)
I prefer to create my own forms as then I know what I'm getting!
 

moke123

AWF VIP
Local time
Today, 02:15
Joined
Jan 11, 2013
Messages
3,852
:eek:Wow, not sure I ever noticed it before.
I may have but ignored them because they all use the formatted layout grids which drive me crazy.
I assume they dont return a value like a standard message box so that needs to be coded in.
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
:eek:Wow, not sure I ever noticed it before.
I may have but ignored them because they all use the formatted layout grids which drive me crazy.
I assume they dont return a value like a standard message box so that needs to be coded in.

I'll have to leave you to find out ...
As I say I never use these templates mainly because I hate layout guides
 

access2010

Registered User.
Local time
Yesterday, 23:15
Joined
Dec 26, 2009
Messages
1,019
Thank you to everyone who tried to help us.
The person who takes care of our databases returned to-day after an illness and fixed our problems,
A = removed the "<" in the format field
B = added an After Update Event
===
Private Sub Contact_AfterUpdate()
Me.Contact = StrConv(Me.Contact, 3)
End Sub
===

Thank you to all you nice people and may you have an enjoyable new year.
Nicole
 

isladogs

MVP / VIP
Local time
Today, 06:15
Joined
Jan 14, 2017
Messages
18,186
Hi Crystal/Nicole
Excellent. Glad you have a solution.
 

vba_php

Forum Troll
Local time
Today, 01:15
Joined
Oct 6, 2019
Messages
2,884
I changed my mind about talking here. Holy cow, this was an exhausting journey! I'm surprised Nicole stuck around to the end.
 

moke123

AWF VIP
Local time
Today, 02:15
Joined
Jan 11, 2013
Messages
3,852
I changed my mind about talking here. Holy cow, this was an exhausting journey! I'm surprised Nicole stuck around to the end.

Exhausting? I rather liked it.

The OP got their solution.
DBG, Colin,Uncle Gizmo,Gasman ,JDraw all contributed
JDraw linked to a pertinant article
We met ypma

and I was inspired and learned something.

What more could you ask for?
 

Users who are viewing this thread

Top Bottom