My Report's Parameter Query Doesn't Find ALL Results!

English
#1

I am using LibreOffice version 7.5 with FireBird on a Windows 10 desktop.
.
When searching for recipes, my Report’s Parameter Query has problems finding ALL the results. For example:
.
The database only finds 10 (out of 20) recipe names if I search for Bacon using a Report Query that contains the following parameter in the SQL design:

    … AND "tblRecipes"."fldRecipeName" LIKE '%' || :EnterPartialName || '%'

YET, the database finds ALL 20 recipe names that contain the word Bacon if I use the Form-Based Filter in the Recipe Name field to search for: LIKE '%Bacon%'
.
In addition, the Query finds NO results from the set of 5 new records that I have entered since I created the database by importing all the data from Microsoft Access. (But maybe that’s just a coincidence since the query isn’t picking up half the results anyway.)
.
Does anyone know what could be wrong with my parameter Query?

#2

Look for a feature or situation common to all the entries not found, where they are different from the ones found.

  • Do they all have “Bacon” in the beginning, end, space/comma/period before/after, different capitalisation?
  • Are there diacriticals (accents or other modifiers)?
  • Is it a plain text field or rich text (formatting available)?
#3

Thank you, Keme, for your suggestions. Yes, I have looked for situations common to all, and I’m sure that something is there… but I just don’t see anything.

  • The word Bacon is located in all positions throughout the name.
  • There are some commas and dashes, etc. but I tested their removal and it made no difference in the outcome.
  • There are no diacriticals.
  • The field is plain text.
    .
    Perhaps you can spot the trend:
    .
    The Report Parameter Query finds the following 10 recipes:
  • Bacon Quiche Cups to Go - SBD
  • Bacon and Tomato Pasta
  • Baked Brown Rice with Bacon Dressing
  • Cornbread Casserole, Bacon & Cheddar - 5-Heart Home
  • Fried Rice with Canadian Bacon
  • Macaroni and Cheese with Bacon - Henderson
  • Mini Chicken Pot Pies with Bacon and Marjoram
  • Nutty Greens with Bacon and Blue Cheese
  • Pulled Cuban Chicken Wraps And Cheese Fries With Bacon
  • Salad, Beef, Leek and Tomato - with Bacon Dressing
    .
    But the Form-Based Filter finds ALL 10 recipes above PLUS the following 10 recipes that the Query does NOT:
  • Microwave Bacon Omelet
  • Egg, Bacon, and Tomato Breadless Sandwiches
  • Bacon and Egg Salad
  • Pizza with Caramelized Onions and Crispy Bacon
  • Chicken with Cider and Bacon Sauce
  • Baked Eggs in Canadian Bacon Cups
  • Bacon, Turkey
  • Canadian Bacon, French Toast, and Cherries
  • Sandwich, Grilled Cheese w Tomatoes and Bacon
  • Bacon, Baked - TheKitchen
    .
    As you can see, both sets contain a few dashes and commas, but they are also in the names that the Query DOES find.
    .
    Does any noticeable trend stand out?
#4

Just a thought… could something ELSE in my Query cause it to not find some results? Here is the complete query if anyone sees an obvious issue with it. The Parameter is located in the next-to-last line.
.

SELECT "tblRecipes"."fldRecipeName"
 	, "tblRecipes"."fldRecipeID"
 	, "tblRecipes"."fldSource"
 	, "tblRecipes"."fldRecipeDescription"
 	, "tblRecipeIngredients"."fldQuantity"
 	, "tblRecipeIngredients"."fldQtyMeasurement"
 	, "tblIngredients"."fldIngredientName"
 	, "tblRecipeIngredients"."fldIngredientType"
 	, "tblRecipeIngredients"."fldComments"
 	, "tblFoodCategories"."fldFoodCategoryID"
 	, IIf( "fldTimeToPrep" IS NULL, '', 'Prep Time: ' || "fldTimeToPrep" ) "PrepTime"
 	, 'Serves: ' || "fldServingNumber" "Serves", 'Serving Size: ' || "fldServingSize" "ServingSize"
 	, "tblRecipes"."fldProcedure"
 	, "tblRecipes"."fldRecipeNotes"
 	, 'Serve With: ' || "fldServeWith" "ServeWith"
 	, IIF( "fldType_SouthBeachDiet" = TRUE, 'South Beach Diet Phase: ', '' ) "Message"
 	, IIF( "fldType_SouthBeachDiet" = TRUE, "fldType_SBDPhaseNo", NULL ) "Message2"
 	, "tblRecipes"."fldImagePath"
 	, 'Utensils: ' || "fldUtensilsNeeded" "Utensils"
 	, "tblFoodCategories"."fldFoodCategoryName" 
 FROM "tblRecipeIngredients", "tblIngredients"
 	, "tblRecipes"
 	, "tblFoodCategories"
 	, "tblEthnicity" 
 WHERE "tblRecipeIngredients"."fldRI_IngredientID" = "tblIngredients"."fldIngredientID"
 	AND "tblRecipes"."fldFoodCategoryID" = "tblFoodCategories"."fldFoodCategoryID" 
 	AND "tblRecipes"."fldType_EthnicOrigin" = "tblEthnicity"."fldEthnicityID" 
 	AND "tblRecipeIngredients"."fldRI_RecipeID" = "tblRecipes"."fldRecipeID" 
 	AND "tblRecipes"."fldRecipeName" LIKE '%' || :EnterPartialName || '%' 
 ORDER BY "tblRecipes"."fldRecipeName" ASC

.
This is based on the following table relationships:

image
image1345×792 84 KB

#5

You could test if it is part of the query: Delete

AND "tblRecipes"."fldRecipeName" LIKE '%' || :EnterPartialName || '%'

and start the query again. Are all fields you expect part of the query? Could you see Bacon, Baked - TheKitchen also. By the way:

AND LOWER("tblRecipes"."fldRecipeName") LIKE '%' || LOWER( :EnterPartialName ) || '%'

will help to find all entries, independent of upper or lower cased.

#6 
    … AND UPPER("tblRecipes"."fldRecipeName") LIKE '%' || UPPER(:EnterPartialName) || '%'

bacon.odb (12.3 KB)

#7

THANK YOU, @RobertG , for your helpful suggestions!
.
I removed the tblEthnicity table and that fixed everything! I didn’t realize that I had not assigned so many recipes to an ethnic origin. Evidently, Base doesn’t just leave the field blank like Access does… it excludes the entire record .
.
It was also super helpful to modify the parameter query to LOWER (and UPPER, per @Villeroy).
.
As always, MUCH APPRECIATED!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.