[Solved] Convert input to uppercase

[John_Wayne_I]

I have created a form to put a new adress into the database.
There is a field "information" that should be "Y" for yes or "N" for no.
I want to press the lowercase letter "y" and convert it automatically to uppercase "Y" before sending to the database.
I tried to use the macro capitalise.bsh in different actions but it doesn't do anything.
How can I solve this problem?

[Villeroy]

Change the type of the underlying field to VARCHAR_IGNORECASE, so "y/n" or "Y/N" does not matter. What if someone enters neither "y" nor "n" but any other letter? Are you shure you don't want a boolean field, which allows two states only?

[John_Wayne_I]

The solution with the VARCHAR_IGNORECASE would work but it looks not fine in the report.

But you're right, actual I need a binary field. But I imported this char from an Excel-sheet.
Now I've changed the "y" into "1" and converted the field into integer.
Then I wanted to convert into fix binary but I got a message, that the field couldn't be converted.
I had to delete this field an add at the end of table.
So I tried this and now I have the text <OBJECT> instead of my data in the database.
I created a check box in my form but this insert no data too.


What can I do?

[Villeroy]

Code: Select all

The solution with the VARCHAR_IGNORECASE would work but it looks not fine in the report.

Your report could use a query with a calculated field:
SELECT "Field1", ..., UPPER("FieldX") AS "Info" FROM ...

Binary is for pictures and other things you would store as a separate file otherwise. Boolean fields allow two values Yes and No. In a form they appear as check boxes.

[John_Wayne_I]

Thank you I confounded boolean with binary.

[John_Wayne_I]

Now I tried to use this query in my report, but where can I put the query?

[Villeroy]

Test this query with all double-quoted names adjusted to your needs:
Queries>Create query in SQL view...
Querie's menu: Edit>"Run SQL command directly" (gives you all the functions of the underlying HSQL database engine, avoiding the lousy Base tools).

Code: Select all

SELECT *, CASEWHEN(UPPER("YN")='Y',TRUE,FALSE) AS "Bool"FROM "Table1"

returns all fields from "Table1" plus a calculated field named "Bool" having TRUE for all 'Y' or 'y' in a field named "YN". All other values including Null (empty) return FALSE.
Main menu:Tools>SQL... allows you to run queries to manipulate the database.

Code: Select all

ALTER TABLE "Table1" ADD COLUMN "Bool" BOOLEAN DEFAULT FALSE;

adds a boolean column named "Bool" to "Table1" with a default value of False.

Code: Select all

UPDATE "Table1" SET "Bool"=CASEWHEN(UPPER("YN")='Y',TRUE,FALSE);

Puts a boolean True into new field "Bool" where the field named "YN" is 'Y' or 'y' (derived from our first test-query)

Code: Select all

ALTER TABLE "Table1" ALTER COLUMN "Bool" BOOLEAN NOT NULL;

now we have filled all "Bool" with True/False values we can make "Bool" mandatory for all new values to come. Otherwise the boolean field can have True, False and Null (empty).

Code: Select all

ALTER TABLE "Table1" DROP COLUMN "YN";

Finally we may drop the old "YN" field.
I'm not shure how to change the source of a report. You may replace your report with a new one.

Edit: If you want to map True/False to words Yes/No, use a query as source of your report:

Code: Select all

SELECT ..., CASEWHEN("Bool",'Yes','No') AS "YN" FROM "Table1"

So you end up with a boolean field which allows only for 2 values True/False, putting a default value as specified on field creation. You can map those two values to arbitrary text or numbers by means of queries to be used with reports.

[John_Wayne_I]

Thank you for your detailed answere, but your query

SELECT *, CASEWHEN(UPPER("YN")='Y',TRUE,FALSE) AS "Bool"FROM "Table1"

creates a syntax error.
The mistake is the *. Without the * or replaced by a field the query works correct.

I solved the boolean problem similar by creating a new boolean column. Then I cleared the column with an update "set = 0".
Then I made a query

Code: Select all

update "table" set "newbool" = 1 where "YN" = 'Y'

because I have until now only 'Y'

Now to the last problem:

I'm not shure how to change the source of a report. You may replace your report with a new one.
Edit: If you want to map True/False to words Yes/No, use a query as source of your report:

When I want to create a report I have only one item to select:
I can only use an assistant.

My last open question is:
How can I use a query for my report?
Thank you

[Villeroy]

How can I use a query for my report?

Store the query which includes all the fields and rows you need for the report.
When I call "Use wizard to create report...", the first step of that wizard shows a list of all tables and queries to choose from.

[John_Wayne_I]

Great, that's it.
Thanks.