In a Base Form (Member Master or New Members), I’m using an edit mask X(20) (was A; bad) for last name to capitalize all letters. The problem is - if the last name is short, like Lee, I get 17 spaces after LEE. When creating a concat of last name|| first name It looks really bad in queries and reports. I’ve searched several places and can’t find this issue mentioned. Is there a way to stop the excess spaces at the end of the name yet make sure that the letters entered are uppercase?
Thanks in advance,
Joe
In your SQL, to eliminate left or right spaces, use RTRIM or LTRIM. Your example:
SELECT RTRIM("LastName") || ', ' || "FirstName" from "MemberMaster"
will produce JONES, James
Edit:
Re-read your question & the looked at an original form and queries you presented. Don’t see where the problems are that you mention ( including the use of C). Am I missing something?
Edit 9/5/2017:
Here are some basic instructions for setting up SQL Workbench/J with a split DB: SplitAndWorkbenchJ.odt
Edit 9/7/2017:
Macro Sample - SASMemberMasterMacro.odb
Thank you for this solution. I gave this a try and it works fine, but is there a way to do it at data entry (into the table once) instead of having to do it in every query and view?
Simultaneous posts. The only other way I can see is to abandon the control & use a macro to have the last name always converted to all caps. A sample is on this post. Also read my Edit in answer.
I think since you received my DB, I was asked to change the maintenance/addition of new members so last name will always be uppercase. This is where the problem started. I have now changed all the queries to reflect the RTRIM. I can’t change the Views with the spit DB. I will check each to see if I can delete and replace them, but I think I have one that blocks me from deletion. I’ll go back to your earlier replies to see if there is some insight or tools that I need to review. Thanks again.
Actually, the DB I have (embedded) puts the Last Name in all caps (using the ‘C’) and seems to work fine. That’s why I questioned the whole thing. I did mention how to modify a View - in comment in this post. It’s really not difficult, just protect yourself with a backup.
BTW, have tried to stay away from suggesting macros to you as this opens up a whole new can of worms. But if you want, that’s another direction.
I looked at the post you mentioned above. If I knew how to implement it, I’d be willing to give it a try to see how it works. I’ll study the use of Macros in Forms in the user guides to get some insight. If the Macros are proven code (stuff others have written), then it should be a safe venture.
I downloaded SQL Workbench/J but I’m stuck. It requires JAVA 8 (which I updated) and a JDBC driver. It seems the driver should be the Oracle driver, but Oracle says it only supports ODBC drivers. SQL Workbench says it no longer supports ODBC/JDBC bridge. Any direction for this dummy?
I can understand why you are stuck. Connecting aps is not clear from the beginning. I never completed my personal doc for Workbench/J. Give me a couple of hours and I think I can give you a how to Doc. Oracle does have JDBC but I’ll get you all of that in the doc. Pretty certain this is because of the Views. A bit surprised you didn’t just modify it in the script file.
Well, I guess if I knew I could do that (edit the script file) I’d have saved you a lot of trouble. This old dog is a bit slow to learn. I’m going to try it on a backup copy.
Not a problem. Wanted to document some of it anyway. In my second comment above, I did bring it up again and also provided the link back to the comments where I first brought it up. Guess you just overlooked it. Easy to understand as I get sidetracked also.
Okay, I was able to change all the Views and tested them. Not so tough. Had to be careful to not confuse the header info with the actual SQL. Would still like to learn about the Workbench.
Great. Thank you Great GURU.
OK. So just this once - I told you so! It’s unfortunate most people (unlike you) are not willing to mess with the internals. Safety with backups is the key. You wouldn’t believe how many DB’s I’ve screwed up messing with things!
Workbench installed and working. Now I need to learn how to use it. Your instructions in the 2nd comment were misunderstood by me. I’ve got it now. Now, back to the uppercase macro issue. I downloaded macro you suggested. I’m now reading about the entire macro process.
Happy to hear Workbench is OK. Thanks for testing my instruction sheet.
The macro pointed to should work without modification. The main points you must learn to get it to work is installing it in your Base file & then point the controls’ event (control properties) to the macro. Any problems and I’d be glad to help. Been working with macros for years. You can actually change the control to a plain text box when you use this macro.
Thank you for your offer to help. I’ll certainly take you up on it, as you’ve a great teacher/guide. So now, the macro “pointed to” would not compile, showing a syntax error on line 2:
oTextBox = oEvent.Source.Model I’m stuck here. Also, I created a module in my split DB to store the macro v/s the suggested LO My Macros (Getting Started with Macros user guide). I trust that will make it portable and move with split DB to other users. Is my assumption correct?
Thanks in advance, Joe
Placing the code in a module in the .odb will make it portable. MyMacros will keep it in the installed system (local).
Just terminology - Basic doesn’t actually compile but rather is interpreted line by line.
Base on my testing, you are probably getting an error like ‘Argument is not optional’. This is because you are trying to run the code from the IDE (Run Basic). The way this routine is designed, it needs to originate from the control tripping the Event. Continued next comment.
Here is how I would do this. Feel free to use a copy if not comfortable.
First change the control to a text box. Hold down Ctrl key & right click control (this eliminates selecting the label with the control). Now right click the selected control & select Replace with & select text box. Next, again right click & select Control to get properties. Select Events tab. Select ellipsis (’…’) to right of When losing focus. Next select Macro button.
Expand in left pane under your document until module exposed where you placed macro. Select that module & right pane will show macro(s) in that module. Double click on the macro needed. Select OK, close properties, save form, test. Should work. When you exit the control the contents will be changed to all uppercase characters.