We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

BASE Query Criteria Problem [closed]

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

JoeLibreOffice gravatar image

updated 2021-04-26 14:10:08 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-04-26 14:09:39.029910


@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 +0200 )edit

2 Answers

Sort by » oldest newest most voted

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

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 +0200 )edit

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

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 +0200 )edit

Question Tools

1 follower


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

Seen: 1,144 times

Last updated: Mar 15 '18