BASE Query Criteria Problem

[JoeLibreOffice]

I am having several problems with the migration of my Medical Access database to Base. I think I have migrated the 3 tables from Access to Base, but I am having a problem replicating my queries and reports.

Parameter Query

In Access, I created a query using the following criteria to pull data from the tables based on a user entered selection prompt. I am selecting on the ClaimNum data field.

Like [Enter ClaimNum] & “*”

When I run the Access query, a dialog box appears and I can enter the data. Examples, 1701, 1710A, or using wildcards, 17*. The “17*” entry will pull all of the records beginning with “17”.

In Base, I have not been able to duplicate the wildcard functionality. The ClaimNum data field is VARCHAR length 100, and I also tried VARCHAR_IGNORECASE.

In Base, I entered the following criteria in the ClaimNum data column.

LIKE EnterClaimNum

When I run the query, I get the parameter prompt “EnterClaimNum” and I can enter the selection data. However, this only works if I enter the complete data string. Examples, 1701, or 1710A. The wildcard entry return an empty query. I tried 17*, 17%, 17_, with and without enclosed “ “.

I am using LibreOffice v5.4.5.1 and Java 1.8.0_151 on an XP operating system which I want to migrated to a newer system.

The new system is a dual boot system.

Linux Mint 17.2, LibreOffice v5.4.5.1 and Java 1.7.0_151

Win 7 Pro, LibreOffice v5.4.5.1 and Java 1.8.0_161

The problem occurs on the XP system and both new system configurations.

Thank you in advance for your assistance.

[Ratslinger]

@JoeLibreOffice Just a note. You tag of access2base is not applicable. This actually refers to macro coding. See Access2Base It’s about converting PEOPLE, not data

[Ratslinger]

Hello,

You need to surround the field with % as in:

SELECT * FROM "YOUR_TABLE" WHERE "YOUR_FIELD"  LIKE '%' || :EnterClaimNumber || '%'

[Ratslinger]

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[JoeLibreOffice]

Thank you for your code suggestion, and I deleted the access2base tag that was in error.

When I entered that code into my query criteria SQL the double “%” acted like 2 wildcards. So, entering 17 at the prompt, returned 0017, 0117, xx17…… and all of the 17xx rows from my table. In effect, it seems to be equivalent to %17%.

Removing the ‘%’ || from the beginning of the LIKE statement seems to fix this issue. Entering 17 now returns all records with 17xx rows.

SELECT * FROM “YOUR_TABLE” WHERE “YOUR_FIELD” LIKE :EnterClaimNumber || ‘%’

Can you explain the function of || which I do not understand?

[Ratslinger]

It’s used for string concatenation (recommended in manual instead of ‘+’) in HSQLDB v1.8 (default DB with Base).

Please in future respond using a comment. Answers should be used for response to original question.