Ask Your Question

BASE Query Criteria Problem

asked 2018-03-13 17:53:11 +0100

JoeLibreOffice gravatar image

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.

edit retag flag offensive close merge delete


@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 gravatar imageRatslinger ( 2018-03-13 20:36:17 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-13 18:53:59 +0100

Ratslinger gravatar image


You need to surround the field with % as in:

SELECT * FROM "YOUR_TABLE" WHERE "YOUR_FIELD"  LIKE '%' || :EnterClaimNumber || '%'
edit flag offensive delete link more


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

Ratslinger gravatar imageRatslinger ( 2018-03-15 05:14:35 +0100 )edit

answered 2018-03-15 02:46:02 +0100

JoeLibreOffice gravatar image

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.


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

edit flag offensive delete link more


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.

Ratslinger gravatar imageRatslinger ( 2018-03-15 03:02:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-13 17:53:11 +0100

Seen: 36 times

Last updated: Mar 15