# base form uppercase [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Joe Castor close date 2017-09-08 04:12:30.641212

Sort by » oldest newest most voted

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

more

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?

( 2017-09-05 02:47:44 +0200 )edit

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.

( 2017-09-05 02:54:04 +0200 )edit

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.

( 2017-09-05 03:55:30 +0200 )edit

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.

( 2017-09-05 04:11:43 +0200 )edit

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.

( 2017-09-05 04:14:52 +0200 )edit

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.

( 2017-09-05 04:27:02 +0200 )edit

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?

( 2017-09-05 16:07:24 +0200 )edit

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.

( 2017-09-05 16:19:32 +0200 )edit

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.

( 2017-09-05 19:37:23 +0200 )edit

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.

( 2017-09-05 20:05:09 +0200 )edit